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

MIN/MAX Optimization (Select tables optimized away) does not work for DateTime

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 5.5.33a
    • 5.5.34
    • None
    • None
    • All OS

    Description

      MIN/MAX Optimization (Select tables optimized away) does not work for DateTime.

      This is similar to bug #3855:

      https://mariadb.atlassian.net/browse/MDEV-3855

      However, that bug is specifically about inet_aton.

      This one is regarding datetime values, and they are not optimized away, thus the query can take much longer than expected.

      MariaDB:

      mysql> EXPLAIN SELECT MIN(b) FROM t1 WHERE b <= '2013-11-06 23:59:59';
      +------+-------------+-------+-------+---------------+-------+---------+------+------+--------------------------+
      | id   | select_type | table | type  | possible_keys | key   | key_len | ref  | rows | Extra                    |
      +------+-------------+-------+-------+---------------+-------+---------+------+------+--------------------------+
      |    1 | SIMPLE      | t1    | range | idx_b         | idx_b | 9       | NULL |   11 | Using where; Using index |
      +------+-------------+-------+-------+---------------+-------+---------+------+------+--------------------------+

      MySQL:

      mysql> EXPLAIN SELECT MIN(b) FROM t1 WHERE b <= '2013-11-06 23:59:59';
      +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
      | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
      +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
      |  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |
      +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+

      The following illustrates this:

      CREATE TABLE `t1` (
        `a` int(11) NOT NULL AUTO_INCREMENT,
        `b` datetime DEFAULT NULL,
        PRIMARY KEY (`a`),
        KEY `idx_b` (`b`)
      ) ENGINE=InnoDB;
       
      INSERT INTO `t1` (b) VALUES ('2013-01-06 23:59:59');
      INSERT INTO `t1` (b) VALUES ('2013-02-06 23:59:59');
      INSERT INTO `t1` (b) VALUES ('2013-03-06 23:59:59');
      INSERT INTO `t1` (b) VALUES ('2013-04-06 23:59:59');
      INSERT INTO `t1` (b) VALUES ('2013-05-06 23:59:59');
      INSERT INTO `t1` (b) VALUES ('2013-06-06 23:59:59');
      INSERT INTO `t1` (b) VALUES ('2013-07-06 23:59:59');
      INSERT INTO `t1` (b) VALUES ('2013-08-06 23:59:59');
      INSERT INTO `t1` (b) VALUES ('2013-09-06 23:59:59');
      INSERT INTO `t1` (b) VALUES ('2013-10-06 23:59:59');
      INSERT INTO `t1` (b) VALUES ('2013-11-06 23:59:59');
      INSERT INTO `t1` (b) VALUES ('2013-12-06 23:59:59');
       
      EXPLAIN SELECT MIN(b) FROM t1 WHERE b <= '2013-11-06 23:59:59';

      Attachments

        Activity

          People

            psergei Sergei Petrunia
            ccalender Chris Calender (Inactive)
            Votes:
            1 Vote for this issue
            Watchers:
            6 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.