MySQL optimization people, lend me your ears! - GoFuckYourself.com

StuartD · 04-06-2005, 12:47 PM

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 · 04-06-2005, 12:53 PM

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

adonis · 04-06-2005, 12:55 PM

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

tical · 04-06-2005, 12:56 PM

yup 1 table

StuartD · 04-06-2005, 12:56 PM

Quote:

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 · 04-06-2005, 12:57 PM

a) InnoDB

or

b) Use Mysql's "Handler" Function:

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

Quote:

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 · 04-06-2005, 12:59 PM

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 · 04-06-2005, 01:07 PM

ONE fucking table.

StuartD · 04-06-2005, 01:09 PM

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

JSA Matt · 04-06-2005, 01:12 PM

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

StuartD · 04-06-2005, 01:17 PM

Quote:

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"

04-06-2005, 12:47 PM	#1
StuartD Sofa King Band Join Date: Jul 2002 Location: Outside the box Posts: 29,903	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? __________________ This is me on facebook This is me on twitter

04-06-2005, 12:53 PM	#2
Babaganoosh ♥♥♥ Likes Hugs ♥♥♥ Industry Role: Join Date: Nov 2001 Location: /home Posts: 15,841	Giving the users their own table would be a huge waste. Put them all in one table and index that table properly. __________________ I like pie.

04-06-2005, 12:56 PM	#4
tical Confirmed User Join Date: Feb 2002 Location: Las Vegas Posts: 6,504	yup 1 table __________________ 112.020.756

04-06-2005, 12:59 PM	#7
Robertf Confirmed User Join Date: Feb 2004 Posts: 392	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. __________________ ....

04-06-2005, 01:07 PM	#8
aleck Confirmed User Join Date: Jan 2001 Posts: 940	ONE fucking table. __________________ come trade shemale traffic here

04-06-2005, 12:55 PM	#3
adonis Confirmed User Join Date: Oct 2002 Posts: 231	I would use one table for all. Remember to put "index" on Userid field, this would speed up your queries based on Userid.

04-06-2005, 01:09 PM	#9
StuartD Sofa King Band Join Date: Jul 2002 Location: Outside the box Posts: 29,903	hmm... handler... interesting. That seems to work much better. __________________ This is me on facebook This is me on twitter

04-06-2005, 01:12 PM	#10
JSA Matt So Fucking Banned Join Date: Aug 2003 Location: San Diego, CA Posts: 5,464	A table for every member? I take it you don't plan to have a lot of members?