Results 1 to 10 of 10

Thread: very very slow

  1. #1
    Join Date
    Sep 2009
    Posts
    2

    Unanswered: very very slow

    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

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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?
    George
    Home | Blog

  3. #3
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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.
    Last edited by mike_bike_kite; 09-09-09 at 06:42.

  4. #4
    Join Date
    Jun 2009
    Posts
    5
    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.

  5. #5
    Join Date
    Sep 2009
    Posts
    64

  6. #6
    Join Date
    Sep 2009
    Posts
    2
    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.

  7. #7
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by itman220
    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?

  8. #8
    Join Date
    Sep 2009
    Posts
    64
    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.
    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.

  9. #9
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by mnirwan
    Merge table may be the best solution for you.
    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.

  10. #10
    Join Date
    Sep 2009
    Posts
    64
    Quote Originally Posted by mike_bike_kite
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •