CODEWITHSUNDEEP
mysqlindexingquery-performance
Carlos Navarro Astiasarán
Carlos Navarro Astiasarán

Reputation: 567

How to index a table with ranges?

I have a table with the following fields: id, sex, age_min, age_max, url.

That table will be filtered by sex, age range and id. A common query would be:

SELECT `url` FROM foo WHERE (`id` NOT BETWEEN %lim_1% AND %lim_2%) 
AND `sex`=%sex% 
AND `age_min` <= %age% AND `age_max` >= %age%

My question is: which indexes should I set so that the query response time is optimized? Also, if the query itself can be somehow optimized I would like to know that too.

Upvotes: 1

Views: 163

Answers (1)

O. Jones
O. Jones

Reputation: 108651

This kind of query is hard to speed up spectacularly with an index; the age_min <= %age% AND age_max >= %age% filtering can't be refactored, by the query planner or by the programmer, into a neat little index range scan.

So, you should create a covering index and hope for the best.

You have an equality filter on sex = %sex% so that column should lead the index.

You have half-scan range filters on age_min and age_max. If you know that one of those is more selective than the other, choose it to be the next column in the index. Otherwise just choose one of the two.

Then, include the rest of the table's columns in the index. So you'll get

 (sex, age_min, age_max, id, url)

in the index. The query planner can scan just part of this index, then satisfy the query from the index. If you look up covering index you can learn more about this technique.

Note. If you don't include the sex = %sex% clause in the filter, this index becomes worthless for satisfying that query.

Note. MySQL can almost never use more than one index to satisfy a single WHERE clause. So, putting single-column indexes on lots of columns is almost never a good idea.

Note. If the table contains less than a few hundred rows, indexing it can't hurt, but probably won't help much.

Upvotes: 2

Related Questions