lemmin Posted August 14, 2007 Share Posted August 14, 2007 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted August 15, 2007 Share Posted August 15, 2007 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. Quote Link to comment Share on other sites More sharing options...
lemmin Posted August 15, 2007 Author Share Posted August 15, 2007 Ok, thank you. Quote Link to comment Share on other sites More sharing options...
radalin Posted August 16, 2007 Share Posted August 16, 2007 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.