MySQL Performance: Single table or multiple tables

Question

Hiya,

I have a 8 sets of data of about 30,000 rows for each set, the data is the same structure just for different languages.

The front end of the site will get relatively high traffic.

So my question is regarding MySQL performance, if i should have a single table with one column to distinguish which set the data belongs to (i.e. coloumn "language") or create individual tables for each language set?

(an explanation on why if possible would be really helpful)

Thanks in advance Shadi

Answer 1

I would go with single table design. Seek time, with proper index, should be exactly the same, no matter how "wide" table is.

Apart from performance issues, this will simplify design and relations with other tables (foreign keys etc).

Answer 2

Another drawback to the "one table per language" design is that you have to change your schema every time you add one.

A language column means you just have to add data, which is not intrusive. The latter is the way to go.

Answer 3

I'd go with one-table design too. Since the cardinality of the language_key is very low, I'd partition the table over language_key instead of defining an index. (if your database supports it.)

Answer 4

I agree with the other responses - I'd use of a single table. With regards to performance optimization a number of things have the potential to have a bigger impact on performance:

• appropriate indexing
• writing/testing for query efficiency
• chosing appropriate storage engine(s)
• the hardware
• type and configuration of the filesystem(s)
• optimizing mysql configuration settings

... etc. I'm a fan of High Performance MySQL