[SOLVED] What is more efficient?

[lemmin]

Would it be faster to make individual queries from one thousand tables containing ten rows each, or one table with ten thousand rows where every ten have the same unique category identifying number? Only ten would ever be queried at a time.

 

It would make sense to me that the one thousand tables would be better if I only wanted to query those ten rows, but the problem would be that I would have to create those tables dynamically and I'm not sure what the best method of that is. I was just creating them using the unique category number (ie: table1, table2) but then I can't run queries that are dependant on another table's data without querying it first. Would this small inconvenience be worth it to only have to query a table with ten values? Or is there a better way to name tables dynamically?

 

Thanks.

[Barand]

The one thousand tables solution is fine until you want to join another table to those categories. Then you need  one thousand IF statements to decide which table to join.

 

If I were your DBA I wouldn't even allow dynamic creation of tables (except in certain controlled circumstances).

 

Go for one table with index on category.

[lemmin]

Ok, thank you.

[radalin]

A different way to solve this may be to use another database: PostgreSQL. Rules may be better for such a circumstance. You could create one table with thousand "little tables" in it. While querying only that table will be used and not the rest. And also it will cause any problem with joins I suppose. But I'm not sure about exact properties of rules. Maybe thousand is a big number which will slow down your app. Anyway you could try to google around a bit for more information I think.