Many databases or many tables?

[oxcart]

Hi,

I'm currently developing an application where many users store a lot of data in a MySQL database. This data needs to be queried quickly and often.

The problem is that the queries are already becoming slow even though we are expecting the number of users to increase tenfold.

I'm considering 2 solutions to this problem:

1) Give each user his own MySQL database. Since the data for each user is unrelated, this is a good possibility, but I'm wondering how JBoss will respond to 1000 or more datasources. Will this cause problems?

2) Put the data for each user in separate tables.The MySQL documentation says that this will slow things down somewhat, but I think it will still be an improvement compared to putting everything in a few huge tables.

Does anyone have any experience with these issues?

Thanks in advance for your help.

[rfreedman]

It's fairly unlikely that either solution would work out. I would think that the best idea would be to find the performance problem and fix it.
MySQL is very fast, even with very large databases, if you construct your queries properly.

Here are some suggestions:

1) Make sure that as much database access as possible is done via primary keys. When you have to retreive data by something other than a primary key, make sure that you have a proper index, and that the index is being used (Google MySQL "Explain Plan" for more info).

2) Don't use functions in your WHERE clauses
See "Explain Plan" for this too.

3) Make sure that you don't do any unneccessary database access. Consider some means of caching wherever possible.