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

Ability to use index for order-by and covering index changes with type of join to small table

    XMLWordPrintable

Details

    Description

      In join of 4 tables, optimizer won't use covering index with suitable ordering on first table in case join order of last two tables uses index/table scan on one of them instead of eq_ref - that table has only 2 rows, so optimizer does not seem to feel need to use eq_ref on it, but then resulting plan needs filesort. Happens with and without "LIMIT 3".

      explain
      SELECT evaluation, t.ticket_id
      FROM ticket_game_result g
      JOIN ticket t ON g.ticket_id=t.ticket_id
      JOIN uzivatel u ON (t.user_id=u.user_id)
      JOIN mena c ON c.mena_id=u.mena_id
      WHERE (g.game_id = 2) AND (g.cancelled = 0) AND (t.vyplacen_date > '2013-06-31 22:00:00')
      ORDER BY `g`.`evaluation` DESC LIMIT 3;

      +------+-------------+-------+--------+------------------------------------------------+---------+---------+---------------------------+------+-------------------------------------------------+
      | id   | select_type | table | type   | possible_keys                                  | key     | key_len | ref                       | rows | Extra                                           |
      +------+-------------+-------+--------+------------------------------------------------+---------+---------+---------------------------+------+-------------------------------------------------+
      |    1 | SIMPLE      | g     | ref    | PRIMARY,fk__ticket_game_result__ticket_id,eval | PRIMARY | 2       | const                     |   54 | Using where; Using temporary; Using filesort    |
      |    1 | SIMPLE      | t     | eq_ref | PRIMARY,FK_ticket_1,vyplacen_date              | PRIMARY | 8       | orderby_index.g.ticket_id |    1 | Using where                                     |
      |    1 | SIMPLE      | c     | index  | PRIMARY                                        | PRIMARY | 2       | NULL                      |    2 | Using index; Using join buffer (flat, BNL join) |
      |    1 | SIMPLE      | u     | eq_ref | PRIMARY,FK_mena                                | PRIMARY | 4       | orderby_index.t.user_id   |    1 | Using where                                     |
      +------+-------------+-------+--------+------------------------------------------------+---------+---------+---------------------------+------+-------------------------------------------------+

      explain
      SELECT evaluation, t.ticket_id
      FROM ticket_game_result g IGNORE INDEX (PRIMARY)
      JOIN ticket t ON g.ticket_id=t.ticket_id
      JOIN uzivatel u ON (t.user_id=u.user_id)
      JOIN mena c ON c.mena_id=u.mena_id
      WHERE (g.game_id = 2) AND (g.cancelled = 0) AND (t.vyplacen_date > '2013-06-31 22:00:00')
      ORDER BY `g`.`evaluation` DESC LIMIT 3;

      +------+-------------+-------+--------+----------------------------------------+---------+---------+---------------------------+------+-------------------------------------------------+
      | id   | select_type | table | type   | possible_keys                          | key     | key_len | ref                       | rows | Extra                                           |
      +------+-------------+-------+--------+----------------------------------------+---------+---------+---------------------------+------+-------------------------------------------------+
      |    1 | SIMPLE      | g     | ref    | fk__ticket_game_result__ticket_id,eval | eval    | 3       | const,const               |   54 | Using index; Using temporary; Using filesort    |
      |    1 | SIMPLE      | t     | eq_ref | PRIMARY,FK_ticket_1,vyplacen_date      | PRIMARY | 8       | orderby_index.g.ticket_id |    1 | Using where                                     |
      |    1 | SIMPLE      | c     | index  | PRIMARY                                | PRIMARY | 2       | NULL                      |    2 | Using index; Using join buffer (flat, BNL join) |
      |    1 | SIMPLE      | u     | eq_ref | PRIMARY,FK_mena                        | PRIMARY | 4       | orderby_index.t.user_id   |    1 | Using where                                     |
      +------+-------------+-------+--------+----------------------------------------+---------+---------+---------------------------+------+-------------------------------------------------+

      explain
      SELECT evaluation, t.ticket_id
      FROM ticket_game_result g
      JOIN ticket t ON g.ticket_id=t.ticket_id
      JOIN uzivatel u ON (t.user_id=u.user_id)
      STRAIGHT_JOIN mena c ON c.mena_id=u.mena_id
      WHERE (g.game_id = 2) AND (g.cancelled = 0) AND (t.vyplacen_date > '2013-06-31 22:00:00')
      ORDER BY `g`.`evaluation` DESC LIMIT 3;

      +------+-------------+-------+--------+------------------------------------------------+---------+---------+---------------------------+------+-------------+
      | id   | select_type | table | type   | possible_keys                                  | key     | key_len | ref                       | rows | Extra       |
      +------+-------------+-------+--------+------------------------------------------------+---------+---------+---------------------------+------+-------------+
      |    1 | SIMPLE      | g     | ref    | PRIMARY,fk__ticket_game_result__ticket_id,eval | eval    | 3       | const,const               |   54 | Using where |
      |    1 | SIMPLE      | t     | eq_ref | PRIMARY,FK_ticket_1,vyplacen_date              | PRIMARY | 8       | orderby_index.g.ticket_id |    1 | Using where |
      |    1 | SIMPLE      | u     | eq_ref | PRIMARY,FK_mena                                | PRIMARY | 4       | orderby_index.t.user_id   |    1 |             |
      |    1 | SIMPLE      | c     | eq_ref | PRIMARY                                        | PRIMARY | 2       | orderby_index.u.mena_id   |    1 | Using index |
      +------+-------------+-------+--------+------------------------------------------------+---------+---------+---------------------------+------+-------------+

      Attached is testing dump of 100KB database showing the behavior - on our production server there was one more trouble with plan changing when otherwise unused index is ignored, but I was not able to replicate that on this smaller dataset and I have seen some similar issue already.
      Dump contains analyze table for all 4 tables, because without it after import I was not able to replicate. Plans were totally different.

      Attachments

        Issue Links

          Activity

            People

              psergei Sergei Petrunia
              jkavalik Jiri Kavalik
              Votes:
              1 Vote for this issue
              Watchers:
              5 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.