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

forcing index changes query plan (even if default chose that index)

    Details

      Description

      Noticed in MDEV-6735 (https://mariadb.atlassian.net/browse/MDEV-6735?focusedCommentId=69337&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-69337)

      using ftp://ftp.askmonty.org/public/mdev7786.dump.gz test case:

      MariaDB [test]> explain SELECT t1.f6, t1.f6, t1.f3, t1.f4, t1.f7, t1.f8, t1.f9, DATE_FORMAT( t2.f3, '%Y-%m-%d' ), t3.f5 FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1 AND t2.f3 >= '2015-03-14' AND t2.f3 <= '2015-03-14' LEFT JOIN t3 ON t3.f3=MONTH(t2.f3) ORDER BY t2.f3 limit 300;
      +------+-------------+-------+-------+---------------+------+---------+------+--------+------------------------------------------------+
      | id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows   | Extra                                          |
      +------+-------------+-------+-------+---------------+------+---------+------+--------+------------------------------------------------+
      |    1 | SIMPLE      | t1    | ALL   | NULL          | NULL | NULL    | NULL | 110000 | Using temporary; Using filesort                |
      |    1 | SIMPLE      | t2    | range | idx1,idx2     | idx2 | 3       | NULL |   1117 | Range checked for each record (index map: 0x6) |
      |    1 | SIMPLE      | t3    | ref   | idx3          | idx3 | 4       | func |   1477 | Using where                                    |
      +------+-------------+-------+-------+---------------+------+---------+------+--------+------------------------------------------------+
      3 rows in set (0.00 sec)
       
      MariaDB [test]> explain SELECT t1.f6, t1.f6, t1.f3, t1.f4, t1.f7, t1.f8, t1.f9, DATE_FORMAT( t2.f3, '%Y-%m-%d' ), t3.f5 FROM t1 LEFT JOIN t2 force index(idx2) ON t2.f1 = t1.f1 AND t2.f3 >= '2015-03-14' AND t2.f3 <= '2015-03-14' LEFT JOIN t3 ON t3.f3=MONTH(t2.f3) ORDER BY t2.f3 limit 300;
      +------+-------------+-------+-------+---------------+------+---------+------+--------+-------------------------------------------------+
      | id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows   | Extra                                           |
      +------+-------------+-------+-------+---------------+------+---------+------+--------+-------------------------------------------------+
      |    1 | SIMPLE      | t1    | ALL   | NULL          | NULL | NULL    | NULL | 110000 | Using temporary; Using filesort                 |
      |    1 | SIMPLE      | t2    | range | idx2          | idx2 | 3       | NULL |   1117 | Using where; Using join buffer (flat, BNL join) |
      |    1 | SIMPLE      | t3    | ref   | idx3          | idx3 | 4       | func |   1477 | Using where                                     |
      +------+-------------+-------+-------+---------------+------+---------+------+--------+-------------------------------------------------+

      Even though idx2 was chosen for t2 already, forcing it changed the query plan (in this case for the better but hopefully the MDEV-6735 patch will fix that).

      Does method of use on the index need to be specified in the force index?

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                psergey Sergei Petrunia
                Reporter:
                danblack Daniel Black
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: