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

Full table is read, but most of the results are discarded right after that.

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.1, 10.2, 10.3
    • 10.4
    • None

    Description

      based on https://bugs.mysql.com/bug.php?id=79040

      create table t1 (i int);
      insert into t1 select seq from seq_1_to_1000;
      select * from ( select * from t1)dt limit 1;  # select `test`.`t1`.`i` AS `i` from `test`.`t1` limit 1
      select * from ( select * from t1 group by i)dt limit 1; #/* select#1 */ select `dt`.`i` AS `i` from (/* select#2 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` group by `test`.`t1`.`i`) `dt` limit 1
      

      MariaDB [test]> explain extended select * from (select * from t1 group by i)dt;
      +------+-------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
      | id   | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                           |
      +------+-------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
      |    1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 |                                 |
      |    2 | DERIVED     | t1         | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 | Using temporary; Using filesort |
      +------+-------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
      2 rows in set, 1 warning (0.001 sec)
       
      MariaDB [test]> explain extended select * from (select * from t1)dt;
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
      |    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 |       |
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
      1 row in set, 1 warning (0.001 sec)
       
      MariaDB [test]> select * from (select * from t1)dt limit 1;
      +------+
      | i    |
      +------+
      |    1 |
      +------+
      1 row in set (0.001 sec)
       
      MariaDB [test]> show status like '%Handler_read_rnd%';
      +--------------------------+-------+
      | Variable_name            | Value |
      +--------------------------+-------+
      | Handler_read_rnd         | 0     |
      | Handler_read_rnd_deleted | 0     |
      | Handler_read_rnd_next    | 1     |
      +--------------------------+-------+
      3 rows in set (0.002 sec)
       
      MariaDB [test]>  select * from ( select * from t1 group by i)dt limit 1;
      +------+
      | i    |
      +------+
      |    1 |
      +------+
      1 row in set (0.067 sec)
       
      MariaDB [test]> show status like '%Handler_read_rnd%';
      +--------------------------+-------+
      | Variable_name            | Value |
      +--------------------------+-------+
      | Handler_read_rnd         | 1000  |
      | Handler_read_rnd_deleted | 0     |
      | Handler_read_rnd_next    | 2004  |
      +--------------------------+-------+
      

      based on https://bugs.mysql.com/bug.php?id=92994

      create table t1 (i int);
      insert into t1 select seq from seq_1_to_1000;
       
      (select * from t1 limit 100) union all (select * from t1 limit 100) limit 1;
      explain extended (select * from t1 limit 100) union all (select * from t1 limit 100) limit 1;
      

      MariaDB [test]> explain extended (select * from t1 limit 100) union all (select * from t1 limit 100) limit 1;
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
      |    1 | PRIMARY     | t1    | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 |       |
      |    2 | UNION       | t1    | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 |       |
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
      2 rows in set, 1 warning (0.003 sec)
       
      MariaDB [test]> flush status;
      Query OK, 0 rows affected (0.001 sec)
       
      MariaDB [test]> (select * from t1 limit 100) union all (select * from t1 limit 100) limit 1;
      +------+
      | i    |
      +------+
      |    1 |
      +------+
      1 row in set (0.008 sec)
       
      MariaDB [test]> show status like '%Handler_read_rnd%';
      +--------------------------+-------+
      | Variable_name            | Value |
      +--------------------------+-------+
      | Handler_read_rnd         | 0     |
      | Handler_read_rnd_deleted | 0     |
      | Handler_read_rnd_next    | 200   |
      +--------------------------+-------+
      3 rows in set (0.006 sec)
      
      

      Attachments

        Issue Links

          Activity

            People

              psergei Sergei Petrunia
              alice Alice Sherepa
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.