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

Optimizer uses wrong index

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 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. dump.sql.tgz.encaa
          9.00 MB
        2. dump.sql.tgz.encab
          9.00 MB
        3. dump.sql.tgz.encac
          9.00 MB
        4. dump.sql.tgz.encad
          9.00 MB
        5. dump.sql.tgz.encae
          9.00 MB
        6. dump.sql.tgz.encaf
          9.00 MB
        7. dump.sql.tgz.encag
          9.00 MB
        8. dump.sql.tgz.encah
          9.00 MB
        9. dump.sql.tgz.encai
          1.62 MB

          Activity

            People

            Assignee:
            psergei Sergei Petrunia
            Reporter:
            kjoiner Kyle Joiner
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated: