Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

What is the best way to index a table that I query using independent range conditions?
E.g. WHERE X < ? and Y > ?

share|improve this question
    
What are the datatypes of columns X and Y? –  ypercube Aug 11 '13 at 8:50
    
@ypercube: X is a VARCHAR and Y is a DATE –  Cratylus Aug 11 '13 at 8:59
    
I don't know much about MySQL but in general that query could be resolved by performing an index intersection on two separate indexes on X and on Y. Or performing a range seek on the most selective of the two conditions and evaluating the other condition as a residual predicate. –  Martin Smith Aug 11 '13 at 13:15
add comment

2 Answers

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

X is a VARCHAR and Y is a DATE

For the sake of example, we'll use this query

SELECT A,B FROM mydb.mytable
WHERE X < ? and Y > ? ;

You need to create an index, but you must examine which order of columns forms the smaller range for a first column. Is it (X,Y) or (Y,X) ?

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:

SELECT COUNT(DISTINCT Y) DateCount FROM mydb.mytable WHERE Y > ?
SELECT COUNT(DISTINCT LEFT(X,2)) CharCount FROM mydb.mytable WHERE X < ?

Which combination forms the smaller set? This would be hard to judge if you have something lopsided like X < 'Umbrella' or Y > '1929-10-29'. These are ranges too unreasonable for a query optimizer to want to use an index.

If the date range is short, create an index with (Y,X)

If the string value range is short, create an index with (X,Y)

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.

SELECT A,B FROM mydb.mytable
WHERE X < ? and Y > ? ;

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:

ALTER TABLE mydb.mytable ADD INDEX idx1 (Y,X,A,B);
ALTER TABLE mydb.mytable ADD INDEX idx2 (X,Y,A,B);

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

SUMMARY

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 ANALYZE TABLE mydb.mytable; if the table experiences heavy INSERTs, UPDATEs, and DELETEs.

share|improve this answer
add comment

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:

SELECT ... WHERE X<?;

and

SELECT ... WHERE Y>?;

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 DATE column. You might also be able to create both indexes, but that might make things worse too, depending how flexible the optimizer is.

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.

share|improve this answer
    
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. But won't this test be conclusive only to the specific dataset? –  Cratylus Aug 11 '13 at 18:53
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.