MySQL Forums
Forum List  »  Optimizer & Parser

45 million row table - Two BETWEENs not using a composite index optimally
Posted by: Tasman Hayes
Date: August 06, 2007 12:42AM

45 million row MyISAM table in MySQL 5.1.20.

select * from M
where B between 726642 and 733649
and C between 8224 and 8230;

Result set has 311 rows.

Full table scan: 32.08 seconds.
Using index on (B): 37.90 seconds.
Using index on (B,C): 39.75 seconds.
Potential performance: 0.01 seconds.

I'd hoped that MySQL would apply the composite index (B,C), with an index range scan on B, and a subindex range scan on C (for each matching B). But it seems not. The similarities between the response times for using the index on (B) and the index on (B,C), indicate MySQL is probably index range scanning on B, and then looking at every C element and applying a WHERE filter. That is, MySQL does not seem to take advantage of the fact that for each matching B, the C elements are available in the index in sorted order, and so could be subindex range scanned, rather than a full subindex scan.

It seems MySQL scans 4,776,432 index entries, rather than just the 74,865 index entries it needs to examine.

To check this, I collapsed the ranges, by making all the numbers in each range just the first number:
UPDATE m SET b = 726642 WHERE b BETWEEN 726642 AND 733649;
UPDATE m SET c = 8224 WHERE c BETWEEN 8224 AND 8230;

Now I run the query as: select * from M where B = 726642 and C = 8224;
This returns in 0.01 seconds.
If the BETWEENS were more cleverly optimized by MySQL, the original query should return in the same time - 0.01 seconds.

Is my understanding correct?
Is there any way to get MySQL to optimally use a composite index (index range scan plus subindex range scan) to satisfy two BETWEEN statements?

We cannot easily flatten these ranges into single numbers. These ranges encode a complex data structure. Flattening the ranges would involve many code changes and many extra columns and many extra indexes.

Thanks for your expertise and illumination,
Tasman

Options: ReplyQuote


Subject
Views
Written By
Posted
45 million row table - Two BETWEENs not using a composite index optimally
5159
August 06, 2007 12:42AM


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.