MySQL optimization people, lend me your ears!
 

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?

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

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

yup 1 table

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?

a) InnoDB

or

b) Use Mysql's "Handler" Function:

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


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 ;)

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.

ONE fucking table.

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

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"