How can I optimize my database?

Question

Hi, I am creating a platform for some clients. Each client needs to have contacts and manage them in groups, categories (which depends of the group) and subcategories (which depends of the category).

The database is going to be very big, and Im afraid about the performance. I want to optimize the database; now, I have these options:

1. Manage only one database with multiple tables (as we manage now)
2. Create a database for each client (each database will have the same multiple tables as the option 1)
3. Manage multiple XML files (like option 2, each client will have a directory with an XML for contacts, another XML file for groups, another for categories, and so on)

Wich is the best option for performance and management of the data (CRUD, create, read, update, delete)??

Thanks!!

Answer 1

3 is not advisable. Search etc. is not what XML files do efficiently.

2 is a maintenance problem.

1 should be doable. "very big" means what? I have a database with a tabe with currently 1.5 billion entries - that is "big" not "very big". What do you define as very big?

Answer 2

I think one database with multiple tables is the way to go, because duplicating the database and schema for each new client doesn't scale well. XML files sounds cool but so far I haven't seen an XML read/write engine which is as fast as most RDBMSes, so bin that one.

To make this work (lots of tables in one database) you should pay attention to indexing and optimizing the one database; indexes in particular will help you maintain speed as you scale up.

Answer 3

Use clustered indexing on the clienId in whichever table it might exist as a foreign key. This procedure will give you the best client-centric performance because you would (usually) be pulling a particular client's info in a page fetch.

For #2, I would suggest making that a premium service to your clients. If they want "priority hosting" on a separate server of "their own" then they pay extra. That will make the maintenance headache worthwhile.

Answer 4

Have you tried actually implementing 1 (which is the easiest)?

1. Did you profile the code?
2. What is the performance now?
3. use EXPLAIN to see how the queries are performing?
4. Do you use indexes (often correct indexes are enough to give excellent performance changes)?

Optimize when you hit a bottleneck (or when you set certain benchmarks for performance), not during design phase...

UPDATE: You mentioned "millions of entries". That's nothing for mysql (provided you use correct indexes on your tables). I have a table with about 40 million rows & although it's not lightning fast it gives me results in a couple of seconds. So there you go...

Answer 5

As far as ongoing maintenance and support goes I think only option 1 makes sense for you. Index all columns you need to but nothing more. Look at your code and see how tables are being JOINed and index the columns which will otherwise require a table scan.

Indicies will speed up the read operations but slow down your write operations as you need to update the indicies as well as the column. They also need more space in the DB.

As suggested above use EXPLAIN to see how your queries are executing and what can be optimized there.

Finally performance tuning only works well after you baseline your existing performance, make a change, then baseline performance again to see if it helped. If not roll back and try something else. But always start with a known level of performance, otherwise you might end up making multiple changes which in total slow things down. Good luck!