MySQL Forums
Forum List  »  Newbie

Re: Tune that query (and multi INdexes)
Posted by: Jay Pipes
Date: June 29, 2005 08:16PM

Erin,

Both actually look like optimal query plans. Remember that if you do not see 'Using index' in the Extra column, that does not mean that the index was not used, but rather, if 'Using index' shows in the Extra column, it means that a covering index was available (meaning, all fields needed by the query are available in the index leaves), then the index (.MYI) file was used to retrieve the data, avoinding the record lookup into the .MYD file when the index is used as a filter (your second EXPLAIN in your second post).

If either of these queries is taking more than milliseconds, there may be something else afoot than this particular query. The problem with the slow query log is that it sometimes lead one to believe that the query is executing slowly *all* the time, when in fact the query only executed slowly once (perhaps while the audio_file table was locked during an extended update or on a FULLTEXT entry insert...)

The point is, do benchmarking on the query using supersmack or Zawodny's MyBench (if you're good with Perl), and figure out if the query is truly performing poorly under normal conditions. When you do benchmarking, ensure that you run tests on a non-production machine and turn off the query cache to isolate the variables tested (in this case, your query).

I have to think that this query will perform lightning fast 99.999% of the time. If you find it does not, and run some smacks against it showing poor performance, post back here, or consider asking for some assistance through the MySQL Network. Flagrant plug: Chapter 6 of our book covers numerous benchmarking and profiling techniques for both MySQL and using the APD Zend extension for profiling PHP scripts (like the poorly coded application we've been talking about...)

OK, I'm off to finish writing my reply to your question about the table_cache... :)

Jay Pipes
Community Relations Manager, North America, MySQL Inc.

Got Cluster? http://www.mysql.com/cluster
Personal: http://jpipes.com

Options: ReplyQuote


Subject
Written By
Posted
Re: Tune that query (and multi INdexes)
June 29, 2005 08:16PM


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.