Quick question

[lampstax]

Just wonder what the difference is, performance wise, between having 10k record stored in 10 tables vs 100k record store in 1 table.

 

Lets assume we're talking about a popular forum with 10 sub forums.  Postings is the table that currently contains all the messages and posting.

 

Is there any benefit to splitting 'Posting' off to 10 tables each containing a unique sub forum's postings ?

 

SQL wise, its pretty much

 

SELECT * FROM postings WHERE subforumid = 'x' ORDER BY updated_date DESC

 

VS.

 

SELECT * FROM postings_subforumx ORDER BY updated_date DESC

 

What do you guys think ?

[Xurion]

In my experience, you'll need a -lot- more data than that to slow anything down (depending on the server). My company database has 19m records and there is still no visual sign of lack of speed.

[lampstax]

Agreed.  Alot more data is needed.  It was just a hypothetical question.

 

Lets raise it to 10m in 10 tables or 100m in 1 table then.

[Zwiter]

from what i know ( i dont have experienced that), its better to have one single table with the right index or indexes.

I believe that a good index is the key of the performance, whatever the number of records in.

 

Another important notion is normalization :

Use the 3rd normal form, with index on primary keys and foreigner keys, and you should have nice performances.

 

Z.

[Xurion]

Limits to data are dependent on the hardware the database runs on.

[fenway]

Mysql 5.1 has partioning for this very reason.