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

Optimizer uses wrong index

    XMLWordPrintable

Details

    • Bug
    • Status: Stalled (View Workflow)
    • Critical
    • Resolution: Unresolved
    • 10.3.28, 10.4.18, 10.7
    • 10.4
    • Optimizer
    • None
    • Centos 7

    Description

      The optimizer chooses the wrong path for a query that should use the primary key:

      MariaDB [cs0275577]> explain SELECT d.id, d.dname, c.id, c.cname, c.state FROM deployment d join host c on d.id=c.deployment_id order by c.id limit 1;
      +------+-------------+-------+------+-----------------------+-----------------------+---------+----------------+-------+---------------------------------+
      | id   | select_type | table | type | possible_keys         | key                   | key_len | ref            | rows  | Extra                           |
      +------+-------------+-------+------+-----------------------+-----------------------+---------+----------------+-------+---------------------------------+
      |    1 | SIMPLE      | d     | ALL  | PRIMARY               | NULL                  | NULL    | NULL           | 97470 | Using temporary; Using filesort |
      |    1 | SIMPLE      | c     | ref  | cluster_fk_deployment | cluster_fk_deployment | 767     | cs0275577.d.id | 6     |                                 |
      +------+-------------+-------+------+-----------------------+-----------------------+---------+----------------+-------+---------------------------------+
      2 rows in set (0.001 sec)
      

      MariaDB [cs0275577]> explain SELECT d.id, d.dname, c.id, c.cname, c.state FROM host c straight_join deployment d on d.id=c.deployment_id order by c.id limit 1;
      +------+-------------+-------+--------+-----------------------+---------+---------+---------------------------+------+-------+
      | id   | select_type | table | type   | possible_keys         | key     | key_len | ref                       | rows | Extra |
      +------+-------------+-------+--------+-----------------------+---------+---------+---------------------------+------+-------+
      |    1 | SIMPLE      | c     | index  | cluster_fk_deployment | PRIMARY | 767     | NULL                      | 1    |       |
      |    1 | SIMPLE      | d     | eq_ref | PRIMARY               | PRIMARY | 767     | cs0275577.c.deployment_id | 1    |       |
      +------+-------------+-------+--------+-----------------------+---------+---------+---------------------------+------+-------+
      2 rows in set (0.000 sec)
      

       
      MariaDB [cs0275577]> SELECT d.id, d.dname, c.id, c.cname, c.state FROM deployment d join host c on d.id=c.deployment_id order by c.id limit 1;
      +--------------------------------------+------------+--------------------------------------+------------+---------+
      | id                                   | dname      | id                                   | cname      | state   |
      +--------------------------------------+------------+--------------------------------------+------------+---------+
      | 4f8cc10b-83ca-41d3-b5b4-9cc74e1292da | depl-73221 | 00000122-e6b5-499d-993f-8052a9272cf3 | cluster-14 | RUNNING |
      +--------------------------------------+------------+--------------------------------------+------------+---------+
      1 row in set (25.254 sec)
      

       
      MariaDB [cs0275577]> SELECT d.id, d.dname, c.id, c.cname, c.state FROM host c straight_join deployment d on d.id=c.deployment_id order by c.id limit 1;
      +--------------------------------------+------------+--------------------------------------+------------+---------+
      | id                                   | dname      | id                                   | cname      | state   |
      +--------------------------------------+------------+--------------------------------------+------------+---------+
      | 4f8cc10b-83ca-41d3-b5b4-9cc74e1292da | depl-73221 | 00000122-e6b5-499d-993f-8052a9272cf3 | cluster-14 | RUNNING |
      +--------------------------------------+------------+--------------------------------------+------------+---------+
      1 row in set (0.002 sec)
      

      Covering indexes did not help the query.

      Attachments

        1. analyze-default.json
          1 kB
          Sergei Petrunia
        2. analyze-straight-join.json
          1 kB
          Sergei Petrunia
        3. dump.sql.tgz.encaa
          9.00 MB
          Kyle Joiner
        4. dump.sql.tgz.encab
          9.00 MB
          Kyle Joiner
        5. dump.sql.tgz.encac
          9.00 MB
          Kyle Joiner
        6. dump.sql.tgz.encad
          9.00 MB
          Kyle Joiner
        7. dump.sql.tgz.encae
          9.00 MB
          Kyle Joiner
        8. dump.sql.tgz.encaf
          9.00 MB
          Kyle Joiner
        9. dump.sql.tgz.encag
          9.00 MB
          Kyle Joiner
        10. dump.sql.tgz.encah
          9.00 MB
          Kyle Joiner
        11. dump.sql.tgz.encai
          1.62 MB
          Kyle Joiner

        Issue Links

          Activity

            People

              psergei Sergei Petrunia
              kjoiner Kyle Joiner (Inactive)
              Votes:
              7 Vote for this issue
              Watchers:
              12 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.