MySQL: Followup on UNION for query optimization, Query profiling
Few days ago I wrote an article about using UNION to implement loose index scan.
First I should mention double IN also works same way so you do not have to use the union. So changing query to:
-
mysql> SELECT sql_no_cache name FROM people WHERE age IN(18,19,20) AND zip IN (12345,12346, 12347);
-
+----------------------------------+
-
| name |
-
+----------------------------------+
-
| ed4481336eb9adca222fd404fa15658e |
-
| 888ba838661aff00bbbce114a2a22423 |
-
+----------------------------------+
-
2 rows IN SET (0.00 sec)
-
-
mysql> EXPLAIN SELECT sql_no_cache name FROM people WHERE age IN(18,19,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 | 9 | USING WHERE |
-
+----+-------------+--------+-------+---------------+------+---------+------+------+-------------+
-
1 row IN SET (0.00 sec)
So as you see there are really different types of ranges in MySQL. IN range allows to optimize lookups on the second key part, while BETWEEN and other ranges do not. Using same access type in EXPLAIN makes it very confusing.
I also was wrong about bug in key length in 5.0 explain. Actually I used tinyint for age and mediumint for zip which makes 4 right answer for using full key.
Be careful however with these nested IN clauses. MySQL has to internally build all possible combinations for row retrieval which ma become very slow if IN lists are large. Take 3 IN lists 1000 values each, on appropriate 3 keyparts and you may finish your lunch before query completes even if table has just couple of rows.
Let me however show how you can profile queries to see what exactly happens during query execution - very helpful for MySQL Performance optimization:
-
mysql> FLUSH STATUS;
-
Query OK, 0 rows affected (0.00 sec)
-
-
mysql> SELECT sql_no_cache name FROM people WHERE age BETWEEN 18 AND 20 AND zip IN (12345,12346, 12347);
-
+----------------------------------+
-
| name |
-
+----------------------------------+
-
| ed4481336eb9adca222fd404fa15658e |
-
| 888ba838661aff00bbbce114a2a22423 |
-
+----------------------------------+
-
2 rows IN SET (0.39 sec)
-
-
mysql> SHOW STATUS LIKE "Handler%";
-
+----------------------------+-------+
-
| Variable_name | Value |
-
+----------------------------+-------+
-
| Handler_commit | 0 |
-
| Handler_delete | 0 |
-
| Handler_discover | 0 |
-
| Handler_prepare | 0 |
-
| Handler_read_first | 0 |
-
| Handler_read_key | 1 |
-
| Handler_read_next | 42250 |
-
| Handler_read_prev | 0 |
-
| Handler_read_rnd | 0 |
-
| Handler_read_rnd_next | 0 |
-
| Handler_rollback | 0 |
-
| Handler_savepoint | 0 |
-
| Handler_savepoint_rollback | 0 |
-
| Handler_update | 0 |
-
| Handler_write | 14 |
-
+----------------------------+-------+
-
15 rows IN SET (0.00 sec)
So you can do FLUSH STATUS to reset counters run the query (assiming your system does not do anything) and run SHOW STATUS to see how counters have changed. It was quite inconvenient you could only do it on idle box so as in MySQL 5.0 you do not have to any more. SHOW STATUS now will show per session counter increments and to get global counters SHOW GLOBAL STATUS needs to be used.
Let us look at this handler statistic - we can see Handler_read_key=1 - this means one index range scan was initiated. Handler_read_next=42250 means 42250 rows were analyzed during this scan. Basically MySQL started scanning Index with age>=18 and continue scanning as soon as it met something larger than 20.
Now let's see what UNION can handle what IN can't:
Lets say we want to show people in appropriate age group sorting by time when they were last online. If age is fixed this works great and it is efficient, however if we have multiple ages to deal with ether as BETWEEN range or as IN filesort appears and query becomes very slow:
-
mysql> EXPLAIN SELECT * FROM people WHERE age=18 ORDER BY last_online DESC LIMIT 10;
-
+----+-------------+--------+------+---------------+------+---------+-------+-------+-------------+
-
| id | select_type | TABLE | type | possible_keys | KEY | key_len | ref | rows | Extra |
-
+----+-------------+--------+------+---------------+------+---------+-------+-------+-------------+
-
| 1 | SIMPLE | people | ref | age | age | 1 | const | 12543 | USING WHERE |
-
+----+-------------+--------+------+---------------+------+---------+-------+-------+-------------+
-
1 row IN SET (0.00 sec)
-
-
mysql> EXPLAIN SELECT * FROM people WHERE age IN(18,19,20) ORDER BY last_online DESC LIMIT 10;
-
+----+-------------+--------+-------+---------------+------+---------+------+-------+-----------------------------+
-
| id | select_type | TABLE | type | possible_keys | KEY | key_len | ref | rows | Extra |
-
+----+-------------+--------+-------+---------------+------+---------+------+-------+-----------------------------+
-
| 1 | SIMPLE | people | range | age | age | 1 | NULL | 37915 | USING WHERE; USING filesort |
-
+----+-------------+--------+-------+---------------+------+---------+------+-------+-----------------------------+
-
1 row IN SET (0.00 sec)
We can however use UNION to avoid filesort of full table:
-
mysql> EXPLAIN (SELECT * FROM people WHERE age=18 ORDER BY last_online DESC LIMIT 10) UNION ALL (SELECT * FROM people WHERE age=19 ORDER BY last_online DESC LIMIT 10) UNION ALL (SELECT * FROM people WHERE age=20 ORDER BY last_online DESC LIMIT 10) ORDER BY last_online DESC LIMIT 10;
-
+----+--------------+--------------+------+---------------+------+---------+-------+-------+----------------+
-
| id | select_type | TABLE | type | possible_keys | KEY | key_len | ref | rows | Extra |
-
+----+--------------+--------------+------+---------------+------+---------+-------+-------+----------------+
-
| 1 | PRIMARY | people | ref | age | age | 1 | const | 12543 | USING WHERE |
-
| 2 | UNION | people | ref | age | age | 1 | const | 12741 | USING WHERE |
-
| 3 | UNION | people | ref | age | age | 1 | const | 12631 | USING WHERE |
-
|NULL | UNION RESULT | <union1,2,3> | ALL | NULL | NULL | NULL | NULL | NULL | USING filesort |
-
+----+--------------+--------------+------+---------------+------+---------+-------+-------+----------------+
-
4 rows IN SET (0.01 sec)
In this case there is also filesort but it applied only to very small table which is result of union, so it is rather fast.
Print This Post
del.icio.us :: 
digg Comment RSS :: Trackback URI
8 Comments
[...] Even though it looks quite similar to previous one it is a lot different as there are multiple category_id values in the list now so index on (category_id, date_created) can’t be used directly. Index on date_created separately would still work. The good from performance standpoint (even though a bit ugly) will be UNION workaround I already wrote about. [...]
Pingback :: September 1, 2006 @ 9:37 am
Peter, I think you are making a mistake regarding the filesort - in both cases (when using “IN (18,19,20)” and when using “UNION”) mysql makes a temporary table of about the same size on which it does a filesort. Basically, in the first version the results are filtered by age and then a temporary table is created with the *filtered* records.
Comment :: December 15, 2006 @ 3:00 am
Mocanu,
Nope there is a serious difference. Assuming you have query which matches 50.000 rows and you want to get and display only 10 of them - a way most search applications work. In case you’re using simply IN you will get 50.000 rows stored in temporary table and sorted by filesort - going to be quite slow.
If you’re using UNION - each of the queries in the UNION will produce only 10 rows, using index for sorting not filesort. If you have 5 queries in the UNION you will have 50 rows in the temporary table which you still need to do filesort for.
Sorting 50.000 rows vs 50 rows is the difference here, now avoiding filesort completely
Comment :: December 15, 2006 @ 3:24 am
Great article…One quaestion : Can I use UNION, if I don’t have certain values for a field ( like age=18, age=19 ) ?? What if I have a query like :
SELECT * from table WHERE type = 1 and value >= 150.4 and value 31 order by score desc limit 20 ???
How can I avoid “using filesort”, in this case ???
Thanx
Comment :: October 29, 2007 @ 9:29 pm
Hi Peter,
I wonder if you might be able to expand on the limitations of using “IN” vs. “BETWEEN” in terms of a point of decreasing returns? (e.g., at what point do the number of values in “IN” become large enough that there is no performance difference between using “IN” and “BETWEEN”?)
Also, does the performance increase involved in using “IN” apply to JOIN queries as well? Is there a difference in how “IN” is treated when the query in question utilizes a JOIN?
Of course, one thing I love about this site is that often times you tell us how we can measure for ourselves. I can see if one query is faster than the other, but am still not so awesome at unwrapping things to determine why.
Thanks.
Comment :: April 8, 2008 @ 5:52 pm
Peter,
one question on UNION vers. IN : Is it a general rule that UNION is faster then IN? Another thing I experienced is a serious speed-advantage when using UNION instead of long OR-Statements. Is this correct?
Thank You!
Comment :: June 13, 2008 @ 7:44 am
Brian,
You would need to test large IN vs BETWEEN this likely to be rather workload dependent.
Note very large IN sets (especially cascaded) may cause MySQL to abandon full range optimization - in this case there is it should become slower than BETWEEN.
Comment :: June 13, 2008 @ 9:55 am
Tobias,
UNION generally has larger overhead than IN or OR. It is faster in the cases when it allows better execution plan - avoiding full table scan with OR on different columns (before 5.0) or avoiding filesort.
Comment :: June 13, 2008 @ 9:57 am