Databases: how much is too much?

[harrylmh]

Hello!
Does having more mysql databases, make data access faster?

I'm just wondering. If I were to have a website that offers users a few services, is it a good idea to have 1 database for each service(storing settings and data) and 1 DB for user information like name and email.

Lastly, can multiple users access and make changes to a DB at the same time.

Please tell me your views.

Thank you

[ashben]

Technically this is called Database Normalization. You may read more about it at:

http://www.phpbuilder.com/columns/barry20000731.php3

http://www.devshed.com/Server_Side/MySQL/Normal

http://www.swynk.com/friends/faour/databasedesign1.asp

And, yes .. multiple users can access/edit database objects since modern Relational DataBase Management Systems (RDBMS) like Oracle, MS-SQL, MySQL, Access etc are multi-threaded, multi-user and support transactions for resource mgmt.

[Mekhu]

Just to extend off the orginal post, If I have a database with 100 entries, would it be quicker to search 4 db's consisting of 25 entries rather than 100 all at once?

Thanks,

Mekhu

[ashben]

Originally posted by Mekhu
Just to extend off the orginal post, If I have a database with 100 entries, would it be quicker to search 4 db's consisting of 25 entries rather than 100 all at once?

That depends upon the structure of the database table, how many fields are present in the table, what indexes have been defined etc. In most cases, it won't make sense to slice a table with a few hundred records.

[harrylmh]

Thanks ashben for those links! I bookmarked one of them for future reference.

I hope you could answer another question from me.

If a table has, say field 1, 2, 3, 4, 5. But 4 and 5 are rarely queried and are queried by the admin. Does this mean that I should seperate the table into 2 tables for faster access, even though I created an index?

Thank you!

[ashben]

Originally posted by harrylmh
If a table has, say field 1, 2, 3, 4, 5. But 4 and 5 are rarely queried and are queried by the admin. Does this mean that I should seperate the table into 2 tables for faster access, even though I created an index?

If the table has a few hundred records then you can leave it as is and rather optimize your SQL queries. As an example: if field 4 & 5 are rarely queried then you may change your SQL queries from:

select * from tablename

.. to ..

select 1,2,3 from tablename

This can be a major performance boost in many cases.

[harrylmh]

Thanks for your input!