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

SELECT @a := MAX(col) FROM t requires full index scan

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 10.1.24, 10.2.9, 10.0(EOL), 10.1(EOL), 10.2(EOL)
    • 10.1.28, 10.2.10
    • Optimizer
    • None

    Description

      MariaDB 10.1.x and 10.2.x do not apply " Select tables optimized away" optimization when MAX(col) value is assigned to a user variable. Consider the following primitive test case:

      openxs@ao756:~/dbs/maria10.2$ bin/mysql -uroot test
      Reading table information for completion of table and column names
      You can turn off this feature to get a quicker startup with -A
       
      Welcome to the MariaDB monitor.  Commands end with ; or \g.
      Your MariaDB connection id is 8
      Server version: 10.2.9-MariaDB Source distribution
       
      Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
       
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
       
      MariaDB [test]> create table test(a int, key(a));
      Query OK, 0 rows affected (0.32 sec)
       
      MariaDB [test]> insert into test values (1), (2), (3);
      Query OK, 3 rows affected (0.14 sec)
      Records: 3  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> explain select max(a) from test;
      +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
      | 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 |
      +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
      1 row in set (0.00 sec)
       
      MariaDB [test]> explain select @a := max(a) from test;
      +------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
      | id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
      +------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
      |    1 | SIMPLE      | test  | index | NULL          | a    | 5       | NULL |    3 | Using index |
      +------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
      1 row in set (0.00 sec)
       
      MariaDB [test]> explain select max(a) into @a from test;
      +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
      | 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 |
      +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
      1 row in set (0.00 sec)
      

      Note that SELECT MAX(col) INTO @a FROM t is NOT affected.

      With any large table this leads to huge difference in query execution time, as full index scan is slow.

      Upstream MySQL 5.6.x is also not affected:

      mysql> create table test(a int, key(a));
      Query OK, 0 rows affected (0.70 sec)
       
      mysql> insert into test values (1), (2), (3);
      Query OK, 3 rows affected (0.26 sec)
      Records: 3  Duplicates: 0  Warnings: 0
       
      mysql> explain select max(a) from test;
      +----+-------------+-------+------+---------------+------+---------+------+-----
      -+------------------------------+
      | 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 |
      +----+-------------+-------+------+---------------+------+---------+------+-----
      -+------------------------------+
      1 row in set (0.12 sec)
       
      mysql> explain select @id := max(a) from test;
      +----+-------------+-------+------+---------------+------+---------+------+-----
      -+------------------------------+
      | 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 |
      +----+-------------+-------+------+---------------+------+---------+------+-----
      -+------------------------------+
      1 row in set (0.02 sec)
       
      mysql> show create table test\G
      *************************** 1. row ***************************
             Table: test
      Create Table: CREATE TABLE `test` (
        `a` int(11) DEFAULT NULL,
        KEY `a` (`a`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8
      1 row in set (0.03 sec)
       
      mysql> select version();
      +------------+
      | version()  |
      +------------+
      | 5.6.29-log |
      +------------+
      1 row in set (0.04 sec)
      

      Attachments

        Activity

          People

            serg Sergei Golubchik
            valerii Valerii Kravchuk
            Votes:
            1 Vote for this issue
            Watchers:
            5 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.