Using UNION to implement loose index scan in MySQL

One little known fact about MySQL Indexing, however very important for successfull MySQL Performance Optimization is understanding when exactly MySQL is going to use index and how it is going to do them.

So if you have table people with KEY(age,zip) and you will run query something like
SELECT name FROM people WHERE age BETWEEN 18 AND 20 AND zip IN (12345,12346, 12347) do you think it will use index effectively ? In theory it could – it could look at each of the ages from the range and look at all zip codes supplied. In practice – it will not:

As you see instead only first index keypart is used (key_len is 4) and zip part where clause is applied after rows are retrived. Notice Using Where. There are even more bad news. Full rows will need to be read to check if zip is in the list, while it could be done only by reading data from the index. MySQL can ether read index only for all rows, in this case you will see “Using Index” in EXPLAIN output or it will read row data for all rows – it can’t read Index and perform row read only if it needs to be done at this point.

So MySQL Will not use indexes in all cases when it is technically possible. For multiple key part indexes MySQL will only be able to use multiple keyparts if first keyparts matched with “=”. Here is example:

Note number of rows has decreased from 90556 to 3, whle “key_len” remains the same. This however looks like a bug in the MySQL 5.0.18 I’m using for this demo. It should have had increased to 8.

Lets see how query times differ in these cases:

As you see difference is tremendous. And it is not what you would intuitively expect – why range which covers 5 rows is hundreds of times slower than single row ? If MySQL Optimizer would handle this case right it would not be but in this case we only can give a hand to MySQL Optimizer and change the query so it can handle it well…. use UNION:

Ethen though this query looks much more complicated MySQL is able to execute it much faster, delivering us expected performance.

You can also use this approach when first key column is not in where clause at all if it has just few values. For example if we would have gender instead of age with just two possible values it would be faster to run such query with union. I bet it would even be so with age even if it would take some 100 queries in the union to do so.

This strategy is best applied if no others work well. Ie if there are range on both keyparts and none of them is selective enough by itself. For example if we would like to only lookup people within single zip I would advice to use index in (zip,age) instead of using this workaround.

And… yes this example is a bit artificial. You would probably use date (or at least year) or birth instead of age, and put zip as first column in the index as it is more selective but it is good enough for illustrative purposes 🙂

Share this post

Comments (16)

  • Michael Otto

    How does SELECT name FROM people WHERE age in (18,19,20,21,22) AND zip IN (12345,12346, 12347); perform here?

    August 11, 2006 at 12:58 pm
  • Dmitry

    Talking about MySQL 5 – what if you had two indices on age and zip separately, how would it look like in explain?

    August 11, 2006 at 1:01 pm
  • peter

    Michael,

    You’re right. This problem seems to be partially fixed and IN works for this case as well as union

    mysql> explain SELECT name FROM people WHERE age in (18,19,20,21,22) AND zip IN (12345,12346, 12347);
    +—-+————-+——–+——-+—————+——+———+——+——+————-+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +—-+————-+——–+——-+—————+——+———+——+——+————-+
    | 1 | SIMPLE | people | range | age | age | 4 | NULL | 16 | Using where |
    +—-+————-+——–+——-+—————+——+———+——+——+————-+
    1 row in set (0.16 sec)

    mysql> SELECT name FROM people WHERE age in (18,19,20,21,22) AND zip IN (12345,12346, 12347);
    +———————————-+
    | name |
    +———————————-+
    | ed4481336eb9adca222fd404fa15658e |
    | 888ba838661aff00bbbce114a2a22423 |
    +———————————-+
    2 rows in set (0.05 sec)

    I actually ment a bit different case but I thought I should simplify it which turned to be not the best way around.
    I’ll post one more post in couple of days to show where union will be the only this which can help you 🙂

    August 11, 2006 at 2:05 pm
  • peter

    Dmitry,

    This is not the case where index merge can apply:

    mysql> explain SELECT SQL_NO_CACHE name FROM people WHERE age BETWEEN 18 AND 22 AND zip IN (12345,12346, 12347);
    +—-+————-+——–+——-+—————+——+———+——+——+————-+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +—-+————-+——–+——-+—————+——+———+——+——+————-+
    | 1 | SIMPLE | people | range | age,zip | zip | 3 | NULL | 19 | Using where |
    +—-+————-+——–+——-+—————+——+———+——+——+————-+
    1 row in set (0.00 sec)

    As you see it can use only one of the indexes it selects to use zip as it is more selective;
    As corelation between indexes is not known it is hard to tell what will be faster to use one of indexes only or
    to intersect row pointers from both indexes.

    If you would need OR in this case Index merge would apply.

    mysql> explain SELECT SQL_NO_CACHE name FROM people WHERE age BETWEEN 18 AND 22 OR zip IN (12345,12346, 12347);
    +—-+————-+——–+————-+—————+———+———+——+——-+—————————————-+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +—-+————-+——–+————-+—————+———+———+——+——-+—————————————-+
    | 1 | SIMPLE | people | index_merge | age,zip | age,zip | 1,3 | NULL | 38821 | Using sort_union(age,zip); Using whe