Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 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?

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.