Details

    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

            Transition Time In Source Status Execution Times
            Roel Van de Paar made transition -
            Open Confirmed
            146d 16h 22m 1
            Sergei Petrunia made transition -
            Confirmed In Progress
            161d 17h 30m 1
            JiraAutomate made transition -
            In Progress Stalled
            649d 4h 39m 1
            Julien Fritsch made transition -
            Stalled Needs Feedback
            442d 1h 56m 1
            Sergei Petrunia made transition -
            Needs Feedback Open
            19d 19h 7m 1
            Sergei Petrunia made transition -
            Open Closed
            53s 1

            People

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

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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