Posted by peter |
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:
SQL:
-
mysql> EXPLAIN SELECT name FROM people WHERE age BETWEEN 18 AND 20 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 | 90556 | USING WHERE |
-
+----+-------------+--------+-------+---------------+------+---------+------+-------+-------------+
-
1 row IN SET (0.01 sec)
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:
SQL:
-
mysql> EXPLAIN SELECT name FROM people WHERE age=18 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 | 3 | USING WHERE |
-
+----+-------------+--------+-------+---------------+------+---------+------+------+-------------+
-
1 row IN SET (0.00 sec)
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:
SQL:
-
mysql> SELECT sql_no_cache name FROM people WHERE age=19 AND zip IN (12345,12346, 12347);
-
+----------------------------------+
-
| name |
-
+----------------------------------+
-
| 888ba838661aff00bbbce114a2a22423 |
-
+----------------------------------+
-
1 row IN SET (0.06 sec)
-
-
-
mysql> SELECT SQL_NO_CACHE name FROM people WHERE age BETWEEN 18 AND 22 AND zip IN (12345,12346, 12347);
-
+----------------------------------+
-
| name |
-
+----------------------------------+
-
| ed4481336eb9adca222fd404fa15658e |
-
| 888ba838661aff00bbbce114a2a22423 |
-
+----------------------------------+
-
2 rows IN SET (1 min 56.09 sec)
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:
SQL:
-
mysql> SELECT name FROM people WHERE age=18 AND zip IN (12345,12346, 12347)
-
-> UNION ALL
-
-> SELECT name FROM people WHERE age=19 AND zip IN (12345,12346, 12347)
-
-> UNION ALL
-
-> SELECT name FROM people WHERE age=20 AND zip IN (12345,12346, 12347)
-
-> UNION ALL
-
-> SELECT name FROM people WHERE age=21 AND zip IN (12345,12346, 12347)
-
-> UNION ALL
-
-> SELECT name FROM people WHERE age=22 AND zip IN (12345,12346, 12347);
-
+----------------------------------+
-
| name |
-
+----------------------------------+
-
| ed4481336eb9adca222fd404fa15658e |
-
| 888ba838661aff00bbbce114a2a22423 |
-
+----------------------------------+
-
2 rows IN SET (0.09 sec)
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 
del.icio.us
digg