mySQL - which is faster - 4 large tables in one database or a database for each?

[giveasyouget]

Tweet

Heya,

Just wondering before I embark on a new project...

Say I want to have several large tables for *fairly* independent sites - all between 200-600mb, all hosted on the same server

Would it be quicker to slap all the tbales in one database or to create an independent database for each one? I'll be doing fulltext searches and the like so load wil get quite high..

[levelbest]

It shouldn't make a lot of difference. Server load would be the same. It would be worth asking your host what the server load is over 5, 10, 15 and 20 minutes.

I would use separate dbases. Its no more difficult and allows more portability.

[axod]

makes no difference. Just means the table files will be stored in a different directory that's all. It'll be just as fast either way.

[haggul]

I'd imagine a slight improvement by splitting it - consider if you had 4 incoming queries at the same time, the much larger table and associated indexes would yield a slightly longer time to resolve each and every query, than the same 4 queries against 4 smaller tables and indexes as they have much less info to wade through.

Also it makes each site independantly redundant from each other - if that large table corrupts for any reason all sites go bang together.

[pricethat]

4 seperate tables would be faster generally and a lot less load on the machine, four seperate databases would actually do the same. Its nice to have everything in one table but then you would probably have to make extra queries to isolate the 4 different bits of info

like SELECT FROM table_name WHERE category = xxxxx

whereas if you put the category of information into one table then it would be

select from table_name

I am not so much of a database techy but i think off the top of my head on one machine when we took the database table up to about 3gb we had serious problems with the machine coping with running http server at the same time during busy periods. When we split the table into two seperate tables it was fine to run two tables both at 2.5gb.

[Rich]

As others have said there should be much difference between one database with 4 tables and 4 databases with one table each as most things work at a table level. I guess one database may be fractionally quicker, but not in any visible way.

If the sites are going to share any data in other tables then I would definitely keep them together.

[axod]

Just incase there was confusion, here's how mysql would store the data on the file system...

/var/lib/mysql/DATABASE1/TABLE1.MYD
/var/lib/mysql/DATABASE1/TABLE2.MYD
/var/lib/mysql/DATABASE1/TABLE3.MYD
/var/lib/mysql/DATABASE1/TABLE4.MYD

vs

/var/lib/mysql/DATABASE1/TABLE1.MYD
/var/lib/mysql/DATABASE2/TABLE2.MYD
/var/lib/mysql/DATABASE3/TABLE3.MYD
/var/lib/mysql/DATABASE4/TABLE4.MYD

So it really won't make a blind bit of difference in terms of speed. It'll just make your SQL a bit longer with the 2nd one if you need to cross reference the tables, as you'll have to specify the database as well as the table in the SQL.