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

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

        Details

        • Type: Bug
        • Status: In Progress
        • Priority: Minor
        • Resolution: Unresolved
        • Affects Version/s: 10.0.5
        • Fix Version/s: 10.0
        • Component/s: None
        • Labels:
          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

            Gliffy Diagrams

              Attachments

                Activity

                There are no comments yet on this issue.

                  People

                  • Assignee:
                    psergey Sergei Petrunia
                    Reporter:
                    pomyk Patryk Pomykalski
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    2 Start watching this issue

                    Dates

                    • Created:
                      Updated: