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

Optimizer uses wrong index

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: In Progress (View Workflow)
    • Priority: Critical
    • Resolution: Unresolved
    • Affects Version/s: 10.7, 10.3.28, 10.4.18
    • Fix Version/s: 10.3, 10.4
    • Component/s: Optimizer
    • Labels:
      None
    • Environment:
      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
        2. analyze-straight-join.json
          1 kB
        3. dump.sql.tgz.encaa
          9.00 MB
        4. dump.sql.tgz.encab
          9.00 MB
        5. dump.sql.tgz.encac
          9.00 MB
        6. dump.sql.tgz.encad
          9.00 MB
        7. dump.sql.tgz.encae
          9.00 MB
        8. dump.sql.tgz.encaf
          9.00 MB
        9. dump.sql.tgz.encag
          9.00 MB
        10. dump.sql.tgz.encah
          9.00 MB
        11. dump.sql.tgz.encai
          1.62 MB

          Issue Links

            Activity

              People

              Assignee:
              psergei Sergei Petrunia
              Reporter:
              kjoiner Kyle Joiner
              Votes:
              6 Vote for this issue
              Watchers:
              10 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.