Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-13275

Query optimizer not picking optimal ORDER BY PRIMARY in case of INNER JOIN on PRIMARY like it does for similar WHERE condition

    XMLWordPrintable

    Details

      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

            Activity

              People

              Assignee:
              psergey Sergei Petrunia
              Reporter:
              joey.mart Joey Mart
              Votes:
              2 Vote for this issue
              Watchers:
              6 Start watching this issue

                Dates

                Created:
                Updated: