Many similar tables

Forum Moderators: open

Message Too Old, No Replies

Many similar tables

ag_47

4:54 am on Jan 30, 2009 (gmt 0)

I've basically got about 30 tables (the # might expand as far as a few hundred), all of which are pretty much exactly the same, except the name.
These are basically a whole bunch of categories that contain similar data. I decided to seperate them into tables because
1. Each user will *mostly* be browsing a certain category, so this way the load is shared among the tables
2 And instead of having 10000s of entries in a single table, all data is divided into 30+ tables, thus making it easier/faster for mysql (ALL queries specify a category). In other words, instead of search through a big table and comparing category names, mysql can just quickly get the category table and worry about other details.

This is OK so far, but I'm a little worried about future updates/maintenance. For now, I keep a file with a list of categories and I use a script to run through these and carry out any necessary updates.
Is there a better way of organizing the database? Any suggestions?

LifeinAsia

4:39 pm on Jan 30, 2009 (gmt 0)

Performance-wise, with proper indexing, I doubt you would see much difference between one table with 10000s of entries or dozens of tables with hundreds of entries. Major DBs are designed to handle millions of entries with ease.

Maintenance-wise, the fewer tables the better.

Just my opinion anyway.

cfx211

4:59 pm on Feb 1, 2009 (gmt 0)

A single table with a type category that has an index on it is a much easier way to handle this. Speed should not matter if you optimize your table correctly and write a good select statement.

It also gives you more flexibility. What if you want to offer parent/child category groupings one day? Having everything in one table will give you that option.

ag_47

7:42 pm on Feb 1, 2009 (gmt 0)

thanks LifeinAsia and cfx211,

so if I make sure all Selects always use 1 or more indexes (depending on the conditions), then performance-wise this is no big deal even if the size of the table grows into the order of 7-8 digits? (I'm going to have a multi-col index, and make sure the order is as needed)
Also I should note that two columns have fulltext indexes on them.
I plan to use Concurrent Inserts, and 'clean up' old entries daily as well as run optimize/analyze table(s).