Jump to content

[SOLVED] What is more efficient?


lemmin

Recommended Posts

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.