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

Optimizer choose different index depends on scan rows in ORDER BY DESC

Details

    • Bug
    • Status: Open (View Workflow)
    • Minor
    • Resolution: Unresolved
    • 10.0.15, 10.4.21
    • 10.4(EOL)
    • Optimizer
    • None

    Description

      I am not sure if this is related to MDEV-6657. Here is the basic table information, note that this is an archived table.

      size: 110G
      number of rows: 700 million

      there are two secondary indexes/columns involving in this issue, one is userid ( INT ), one is access ( datetime )

      example query:

      SELECT * FROM mytable WHERE userid = 123456 ORDER BY access LIMIT 1;

      So if the scan rows are little, optimizer will just use 'userid' index to retrieve the rows.

      +------+-------------+-------+------+---------------+--------+---------+-------+------+-----------------------------+
      | id   | select_type | table | type | possible_keys | key    | key_len | ref   | rows | Extra                       |
      +------+-------------+-------+------+---------------+--------+---------+-------+------+-----------------------------+
      |    1 | SIMPLE      | mytable | ref  | userid        | userid | 4       | const |   49 | Using where; Using filesort |
      +------+-------------+-------+------+---------------+--------+---------+-------+------+-----------------------------+

      However, if we hit some userids that have more historical data, then optimizer intends to use 'access' index to help in sorting and retrieving rows in the same time if my understanding is correct.

      +------+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+
      | id   | select_type | table | type  | possible_keys | key     | key_len | ref  | rows  | Extra       |
      +------+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+
      |    1 | SIMPLE      | mytable | index | userid        | access | 8       | NULL | 24616 | Using where |
      +------+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+

      Apparently, the second execution plan runs forever, > 1 hour, but the first execution plan runs < 7 secs.

      I have to 'FORCE INDEX (userid)' to make sure a stable execution plan, but maybe there is more thing we can consider in this kind of optimization like checking the size of 'access' index before using it or something like that?

      Attachments

        Issue Links

          Activity

            This behavior is related to the optimizer not knowing enough about the selectivity of the non-ordering index in relation to the ordering index's ranges as with MDEV-6813. Sorry for the double post, I messed up the link last time.

            bradjorgensen Brad Jorgensen added a comment - This behavior is related to the optimizer not knowing enough about the selectivity of the non-ordering index in relation to the ordering index's ranges as with MDEV-6813 . Sorry for the double post, I messed up the link last time.

            People

              psergei Sergei Petrunia
              benjaminlin Benjamin Lin
              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.