MySQL optimization people, lend me your ears!

[StuartD]

Alright, I'm doing up a database for a new project and would like to hear your opinion on something.

It's a member base site.... where they enter stuff into the db. No biggy there. Now, is there any benefits other than what's listed below to having each user have their own table?

In other words, instead of having a table that has ALL entries and a "userid" field to index by, is it worth having individual tables for each user: "entries_1", "entries_2", "entries_3" and so on?

The benefits I can see off hand are that if a user's table becomes corrupted or locked or what have you... it won't affect the other users.

The thing that makes me wonder is... would it have any impact on performance? Is it faster to have user's info separated so that there's less "searching" through records or is it slower to have a larger volume of tables to have to reference?

Thoughts?

[Babaganoosh]

Giving the users their own table would be a huge waste. Put them all in one table and index that table properly.

[adonis]

I would use one table for all. Remember to put "index" on Userid field, this would speed up your queries based on Userid.

[tical]

yup 1 table

[StuartD]

Originally Posted by adonis

I would use one table for all. Remember to put "index" on Userid field, this would speed up your queries based on Userid.

Not indexing it would be stupid, so yeah, I do have it indexed.

Has anyone actually tried to test the differences in performance between the two? Seen any actual results either way?

[Trafficbrokercom]

a) InnoDB

or

b) Use Mysql's "Handler" Function:

http://dev.mysql.com/doc/mysql/en/handler.html

There are several reasons to use the HANDLER interface instead of normal SELECT statements:

*

HANDLER is faster than SELECT:
o

A designated storage engine handler object is allocated for the HANDLER ... OPEN. The object is reused for the following HANDLER statements for the table; it need not be reinitialized for each one.
o

There is less parsing involved.
o

There is no optimizer or query-checking overhead.
o

The table doesn't have to be locked between two handler requests.
o

The handler interface doesn't have to provide a consistent look of the data (for example, dirty reads are allowed), so the storage engine can use optimizations that SELECT doesn't normally allow.

Basically what happens is that instead of one query locking the tables eg on a join the query is executed independently for each row and thus avoids locking.

this would be the professional solution ;)

[Robertf]

1 table ...

I run a mainstream membership site with about 25k members at teh moment .. the user table does not only hold name password and stuff but also other variables wich get updated/changed constantly .. and it is no problem.

Special when you want to do search functions you want to have 1 table ..

Incase it becomes to big .. and you need to balance teh load .. make 2 tables a-n m-z .

Incase your usertable .. only holds 'static' information ... username password blah blah .. also make just 1 table .. but have a seperate memory loaded table for the people that are logged in .. (and log them out after a 'set' time of no activity).

Meh did that help?
Anyhow i just tried to say .. iwould go for one table .. because 1 table can easely handle it ..unless you talk about 100k's of members .. and just easier to throw search queries on it.

[aleck]

ONE fucking table.

[StuartD]

hmm... handler... interesting. That seems to work much better.

[JSA Matt]

A table for every member? I take it you don't plan to have a lot of members?

[StuartD]

Originally Posted by JSA Matt

A table for every member? I take it you don't plan to have a lot of members?

nope, not really... a smallish mainstream project. Lots of programming work but not a major database hog... just something that will definitely need to be kept separated from user to user. I don't want them inconvenienced by mysql "glitches"