MySQL Forums
Forum List  »  Performance

Re: Optimizing query & table structure for huge table.
Posted by: James Day
Date: January 19, 2005 12:09AM


select FK_USER,COUNT from PUC where FK_PRODUCT=<constant> order by COUNT desc limit 1,10;

Add the key FK_PRODUCT, COUNT, FK_USER and you will have a covering index (one which provides all data the query needs, so the table records do not need to be read. If this does not improve the result, provide the output of EXPLAIN SELECT... for that query.

The my.cnf value sort_buffer_size can be increased to improve the efficiency of file sorts. A file sort adds enough sorted records to fil the sort buffer size, writes those to disk and repeats, then it processes the parts written to disk in a later pass. To test, you could temporarily make the size as big as the space neded for 3,000 records. Then maybe make it lower if RAM use is a concern. Note that each active query which is doing this sort will use this amount of RAM, so you must consider the total number of connections when selecting a prudent value for this.

1. I (with Wikipedia) have had excellent results with InnoDB and I strongly prefer it to MyISAM for many things but it's not always the best solution. It's easy to change when you have such a small amount of data (only a few gigabytes) so just use alter table engine=innodb and see what happens. Do remember to allocate some RAM to InnoDB and also remember that InnoDB buffers both index and data in RAM, so you should normally increase the buffer RAM you use for InnoDB compared to MyISAM (because MyISAM uses system cache for data records).

2. You didn't say what your current primary key is so it's hard to answer. InnoDB would store records in primary key order, so choosing primary key fields and putting them in the order which matches your most important query an be very helpful. Auto-increment fields are rarely the best choice for this but they are convenient.

3. Try the above suggestions and report what EXPLAIN says.

Options: ReplyQuote

Written By
Re: Optimizing query & table structure for huge table.
January 19, 2005 12:09AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.