Hi

I've a database contains around 100 tables with the same structure. when opening these through a view the performance is extremely slow. it takes more than 12 minutes to open the first 1000 records even on a local pc.

Any suggestions.


BR

If the tables have the same structure, can you tell us what the difference between them is? i.e. why can't you have one bigger table?

If the tables have the same structure then they should just be one table. You can add a type field to say what table they might of gone into. You can see for yourself why MySQL is acting slow with your design: first find a particular word in a single large dictionary (shouldn't take you too long), then find the same word in 100 small dictionaries (takes much longer).

Obviously I don't know what's in your tables but the fact you have a 100 of them all with similar data suggests the design is wrong.

EDIT: sorry george - you must of replied while I was typing.

I recently did some evaluated the difference between a relational model vs. a network model database. I have found that a network model actually has less overhead and takes less IO to perform the same actions than a relational database like MySQL.

You may want to try merge table instead of using view.

See MySQL :: MySQL 5.0 Reference Manual :: 13.3 The MERGE Storage Engine

This is a large billing system. The application holds data based on year and month. and the user may query all data based also on date.

If the data in the tables is simply split by year and month then you could work out what tables you need to access by the the date range required ie if looking at month to date data then just build your query on the current table. Needless to say this type of database design is very poor and will cause lot's of issues. Is there a particular reason you can't put the data into one table?

Like I said before ... Merge table may be the best solution for you. Here's some excerpt from MySQL website:

MERGE tables can help you solve the following problems:

• Easily manage a set of log tables. For example, you can put data from different months into separate tables, compress some of them with myisampack, and then create a MERGE table to use them as one.
• Obtain more speed. You can split a big read-only table based on some criteria, and then put individual tables on different disks. A MERGE table on this could be much faster than using the big table.
• Perform more efficient searches. If you know exactly what you are looking for, you can search in just one of the split tables for some queries and use a MERGE table for others. You can even have many different MERGE tables that use overlapping sets of tables.

I've never used this feature but I agree it seems to offer the easiest solution. However the underlying problem is the database design and I thought it worth querying why the tables were split that way.

I've used this feature at work for tables with billions of rows. However, even then, I only have 4 split tables. You are right. There's underlying problem of having 100 tables.