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

explain for update shows using filesort, but it's not used

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Won't Fix
    • 10.0.5
    • N/A
    • OTHER
    • None

    Description

      Test case:

      CREATE TABLE `t1` (
        `id` int(10) unsigned NOT NULL auto_increment,
        `t` date,
        PRIMARY KEY (`id`),
        KEY `t` (`t`)
      );
       
      INSERT INTO t1 (t) values ('2013-11-14'), ('2013-11-15'),('2013-11-16'),('2013-11-17'),('2013-11-18'),('2013-11-19'),('2013-11-20');
       
      EXPLAIN UPDATE t1 SET t = CURDATE() WHERE id = (SELECT * FROM (SELECT id FROM t1 ORDER BY t ASC LIMIT 1) tmp) LIMIT 1;
      FLUSH STATUS;
      UPDATE t1 SET t = CURDATE() WHERE id = (SELECT * FROM (SELECT id FROM t1 ORDER BY t ASC LIMIT 1) tmp) LIMIT 1;
      SHOW STATUS LIKE 'sort%';

      returns:

      id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
      1       PRIMARY t1      ALL     NULL    NULL    NULL    NULL    7       Using where
      2       SUBQUERY        <derived3>      ALL     NULL    NULL    NULL    NULL    2
      3       DERIVED t1      ALL     NULL    NULL    NULL    NULL    7       Using filesort
       
      SHOW STATUS LIKE 'sort%';
      Variable_name   Value
      Sort_merge_passes       0
      Sort_range      0
      Sort_rows       0
      Sort_scan       0

      Attachments

        Activity

          People

            psergei Sergei Petrunia
            pomyk Patryk Pomykalski
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.