horizontal partitioning or multiple tables?

Question

I'm developing an application and need to propose a database schema.

We plan for a MySQL database with millions of records which should be distributed over multiple machines for performance and availability reasons.

The schema contains an element Category and we will have from 10 to 100 different categories. The number of categories will vary during the system's lifetime. Each query will be relevant for a single category only, and must be ACID-compliant. This means several queries for different categories can (and should!) run in parallel.

I see the following options:

1. Define a single table and partition it horizontally.
2. Use separate tables for each category.

Since I don't have experience in administering database servers and we only have a small amount of categories, I'm wondering which of the two options is easier to administer and what are their other advantages and disadvantages.

Answer 1

I just ran across this old question. In general, administering 1 table would be easier than administering 10-100 tables. Imagine trying to ALTER TABLE across 100 tables!

Just because you have millions of rows doesn't mean you'll need multiple servers. Good queries, good indexes, good disks, etc., are all performance items. Fewer servers are also easier to maintain.

What did you end up doing?