What is the best way to index a table that I query using independent range conditions?
WHERE X < ? and Y > ?
MySQL can do Index Intersection (more commonly know as Index Merging). However, in your case, I have something more reasonable to suggest.
You mentioned in a comment
For the sake of example, we'll use this query
You need to create an index, but you must examine which order of columns forms the smaller range for a first column. Is it
CHOOSING X (VARCHAR) FIRST : This means looking over at least 1296 (36 * 36) possible alphanumeric ranges. Then, within each character range, compare a range of dates up to today's date. 36 is 26 Alphabetic and 10 Numeric.
CHOOSING Y (DATE) FIRST : This means looking a range of dates up to today's date. Then, within each date, compare a range at least 1296 (36 * 36) possible alphanumeric ranges.
You can decide which by running these two queries:
Which combination forms the smaller set? This would be hard to judge if you have something lopsided like
If the date range is short, create an index with
If the string value range is short, create an index with
I would create both indexes and let the Query Optimizer make the decision. Yet, there is something else you can do. Look again at the sample query.
You can boost performance by creating a covering index. That's an index that has all the needed data in the index which bypasses the need to read data from the table. In the case of the above query, you could create the two indexes like this:
With these two indexes, the Query Optimizer will do an index scan in the worst case scenario since all the needed data is already in the index and there is no need to get anything else from the table. This is very beneficial for both MyISAM and InnoDB.
As for Covering Indexes, I mentioned using them in past posts
Dealing with independent ranges may make accurately forecasting which index combination is more relevant can be challenging. Furthermore, imagine the anxiety you will have as to how the query optimizer will evaluate thing. In light of these, you will need to go with covering indexes as much as possible. You will have to also schedule a periodic run of
I believe MySQL cannot execute an index intersection. Therefore you need to find one index to fit your needs.
An index in general is better if its first column reduces the number of matching rows by a lot. For your case lock at a selection of typical parameter pairs. Create two queries by splitting the where clause:
Then run the two queries using the typical parameter pairs. If the first one usually produces fewer rows then the second, an index on (X,Y) will benefit your query most. If the second one usually produces fewer rows then create the index on (Y,X).
If the results are very mixed and sometimes the first produces fewer, sometimes the second produces fewer rows, you probably are better of creating in index leading with the
No matter what you end up deciding, test it in an environment similar to production to make sure it does not cause adverse effects on other queries.