Details
-
Bug
-
Status: Stalled (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.1.24, 10.2(EOL), 10.3(EOL)
-
centos 6
Description
Order by a column that isn't the first column in the join list requires a temporary table.
A query that INNER JOINs two tables on their Primary keys with a WHERE clause on a particular key value properly optimizes to target the first table in the join order for the WHERE clause, presumably based on the logical equivalence of the values implied by the INNER JOIN:
Query second table's id (PRIMARY):
SELECT first.id FROM first INNER JOIN second ON first.id = second.id WHERE second.id > '5E1215B77BB14DA4DD7C9D4DDD26501A';
|
Optimized query (from EXPLAIN EXTENDED warning) points the WHERE clause to the first table:
select `db`.`first`.`id` AS `id` from `db`.`first` join `db`.`second` where ((`db`.`second`.`id` = `db`.`first`.`id`) and (`db`.`first`.`id` > '5E1215B77BB14DA4DD7C9D4DDD26501A'))
|
The same thing does not happen for the ORDER BY, and so changing which table's Primary key we order by makes the difference between requiring a temporary table and not:
This query (ORDER BY first) is basically just an index retrieval:
> ANALYZE SELECT first.id FROM first INNER JOIN second ON first.id = second.id ORDER BY first.id;
|
+------+-------------+--------+--------+---------------+---------+---------+------------------------+------+--------+----------+------------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
|
+------+-------------+--------+--------+---------------+---------+---------+------------------------+------+--------+----------+------------+-------------+
|
| 1 | SIMPLE | first | index | PRIMARY | PRIMARY | 96 | NULL | 100 | 100.00 | 100.00 | 100.00 | Using index |
|
| 1 | SIMPLE | second | eq_ref | PRIMARY | PRIMARY | 96 | huff_20170614.first.id | 1 | 1.00 | 100.00 | 100.00 | Using index |
|
+------+-------------+--------+--------+---------------+---------+---------+------------------------+------+--------+----------+------------+-------------+
|
But if you change the ORDER BY to the second table, it now requires a temporary table:
> ANALYZE SELECT first.id FROM first INNER JOIN second ON first.id = second.id ORDER BY second.id;
|
+------+-------------+--------+--------+---------------+---------+---------+------------------------+------+--------+----------+------------+----------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
|
+------+-------------+--------+--------+---------------+---------+---------+------------------------+------+--------+----------+------------+----------------------------------------------+
|
| 1 | SIMPLE | first | index | PRIMARY | PRIMARY | 96 | NULL | 100 | 100.00 | 100.00 | 100.00 | Using index; Using temporary; Using filesort |
|
| 1 | SIMPLE | second | eq_ref | PRIMARY | PRIMARY | 96 | huff_20170614.first.id | 1 | 1.00 | 100.00 | 100.00 | Using index |
|
+------+-------------+--------+--------+---------------+---------+---------+------------------------+------+--------+----------+------------+----------------------------------------------+
|
In our real-life example, the table was ~2GB and tmp_table_size was 64MB, so this meant that the temp table actually went to disk.
Notable status counter differences:
Handler_commit 1 1
|
Handler_read_first 1 1
|
Handler_read_key 906 906
|
Handler_read_next 906 906
|
Handler_read_rnd 0 906
|
Handler_read_rnd_next 0 907
|
Handler_tmp_write 0 906
|
Attachments
Issue Links
- relates to
-
MDEV-8306 Complete cost-based optimization for ORDER BY with LIMIT
-
- Stalled
-