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...
HTH
Jay Pipes
Community Relations Manager, North America, MySQL Inc.
Got Cluster?
http://www.mysql.com/cluster
Personal:
http://jpipes.com