MySQL Forums
Forum List  »  Optimizer & Parser

Re: Optimizing ORDER BY any ideas?
Posted by: Jay Pipes
Date: September 10, 2005 09:22AM

Marc Van Olmen wrote:
> Hi Jay.
> Side question:
> How you come to the conclusion between index
> *scan* and seek, explained in your book?

The access type displayed by the EXPLAIN output will tell you this (the "type" column) For index seeks, you will see any of the following: eq_ref, ref, ref_or_null, range, index_merge (5+). For index scans, you will see an access type of "index". I know, I know. It's confusing, and has led to numerous folks thinking that an index is being used effectively, when in fact it isn't. Also, you will sometimes see the phrase "using index" show up in the "Extra" column. When this appears, it means that MySQL is able to find all the columns it needs for the query (for the table in question) from the index itself. This means that an additional "bookmark lookup" into the actual .MYD file (where the data records are kept) is not needed. This is known as a "covering index".

> Anyway I remember now that I added before an index
> for T_PROPERTY but with the new import I forgot.
> So I added it now like you suggested but it
> doesn't show much difference ... snip

Well, it definitely *did* make a difference! If you take a look at the EXPLAIN output, you will see that instead of using a TABLE scan (access type: ALL), MySQL instead used an INDEX scan (access type: index) for the ORDER BY query. Because you see the phrase "using index" in the Extra column of the EXPLAIN output (for the ORDER BY query), you know that no additional operations are performed for the bookmark lookups. The query performed faster because of this simple fact: Since the index records are much narrower than the table records, MySQL can search and sort these trimmer records faster. Because the index records are only 80 + 4 bytes wide, versus the table record width (I estimate probably over 1500 bytes per record), MySQL can fit more index records into a single block of memory, making the sort go much faster. In your case, it resulted in a pretty good performance gain (from ~3 seconds to ~1 second, a difference of more than 60%!)

If you *really* want to get these queries to subsecond times, consider breaking out the non-essential information in this table from the essential, NON-NULL fields. Put the less-queried, non-essential info into a separate table, related through a PK-FK relationship. Then, in your queries against the main table, the rest of the non-essential fields don't need to be included by the optimizer... If you don't do this, you'll end up adding a bunch of indexes like the one above every time you ned an ORDER BY query...


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

Got Cluster?

Options: ReplyQuote

Written By
September 06, 2005 05:35PM
September 06, 2005 06:39PM
September 06, 2005 08:30PM
September 06, 2005 09:18PM
September 07, 2005 07:10AM
September 07, 2005 06:28PM
September 07, 2005 08:55PM
September 08, 2005 08:28AM
September 08, 2005 11:12PM
September 09, 2005 10:00AM
September 09, 2005 10:22AM
September 09, 2005 10:03PM
September 10, 2005 07:30AM
Re: Optimizing ORDER BY any ideas?
September 10, 2005 09:22AM
September 12, 2005 02:45PM
September 12, 2005 04:41PM

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.