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

query with order by on indexed column is using filesort

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Cannot Reproduce
    • 5.5.32
    • 10.1.7
    • Optimizer
    • None

    Description

      CREATE TABLE `sort_bug` (
        `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
        `data` datetime NOT NULL,
        `t` smallint(5) unsigned NOT NULL,
        `id_region` smallint(5) unsigned NOT NULL,
        `id_miasto` smallint(5) unsigned NOT NULL,
        `typ_obiektu` smallint(5) unsigned NOT NULL,
        `o` int(10) unsigned NOT NULL,
        `c` int(10) unsigned DEFAULT NULL,
        PRIMARY KEY (`id`),
        KEY `KEY_data` (`data`),
        KEY `o` (`o`)
      ) ENGINE=InnoDB AUTO_INCREMENT=745193 DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;

      With a small amount of rows filesort is used instead of index scan:

      explain SELECT * FROM sort_bug ORDER BY data DESC LIMIT 1;
      +------+-------------+----------+------+---------------+------+---------+------+------+----------------+
      | id   | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra          |
      +------+-------------+----------+------+---------------+------+---------+------+------+----------------+
      |    1 | SIMPLE      | sort_bug | ALL  | NULL          | NULL | NULL    | NULL |  211 | Using filesort |
      +------+-------------+----------+------+---------------+------+---------+------+------+----------------+

      The same is without LIMIT. Analyze table didn't help in this example.
      When LIMIT is small adding a bogus WHERE clause can help:

      explain SELECT * FROM sort_bug where id > 0 ORDER BY data DESC LIMIT 1;
      +------+-------------+----------+-------+---------------+----------+---------+------+------+-------------+
      | id   | select_type | table    | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
      +------+-------------+----------+-------+---------------+----------+---------+------+------+-------------+
      |    1 | SIMPLE      | sort_bug | index | PRIMARY       | KEY_data | 8       | NULL |    1 | Using where |
      +------+-------------+----------+-------+---------------+----------+---------+------+------+-------------+

      Attachments

        1. sort_bug.sql
          12 kB
          Patryk Pomykalski

        Issue Links

          Activity

            People

              sanja Oleksandr Byelkin
              pomyk Patryk Pomykalski
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.