Which one is better for (mysql) performance ?

Question

Which one is better for (mysql) performance ?

"1500 (300x5) Tables with 365 rows per table" OR "(300x5)1500x365 rows in 1 table" ?

(I will get the data(s) with php.)

365 -> days number of the year

• if second: I will use "date" and "x_id" to get the 1 row from (300x5)1500x365 rows.
• if first: I will use "date" and "table_name" to get 1 row from 365 rows.**

Answer 1

Second one. http://datacharmer.blogspot.com/2009/03/normalization-and-smoking.html

[edited after updates to question]

In second case you can create an index on columns (x_id,date) which will improve performance of WHERE x_id = ? AND date = ? searches. Some ~550000 rows is not much for well indexed table.

Answer 2

Assuming that all the relationships in the data are one-to-one, then the second option of using a single table is the better (normalized) approach.

But there's no detail to the multi-table option, or the data that is being stored. Bad design, like a table per user, is responsible for performance - not the fact of numerous tables.

Update

Things are still quite vague, but the data you want to store is daily and over the course of years. What makes it different that you would consider separate tables with identical formats? The identical tables prompts me to suggest single main table, with some supporting tables involved for things like status and/or type codes to differentiate between records in a single table that were obvious in a separate table approach.

Answer 3

That depend on the engine you are using and also if you are going to have more read or more write.

For that look at the way each engine lock the table regarding read and write.

But 1500 table is a lot. I would expect something like 10 table.

1 table is not a bad choice either but if one day you want to have multiple server it gonna be more easy to spread them with 10 table.

Also if you change the structure of the table is going to be long with 1 table. (I know that it shouldn't append but the fact it does)