MySQL Bugs: #24156: Loose index scan not used with CREATE TABLE ...SELECT and similar statements

Bug #24156	Loose index scan not used with CREATE TABLE ...SELECT and similar statements
Submitted:	9 Nov 2006 21:59	Modified:	14 Dec 2006 4:26
Reporter:	Harrison Fisk	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S5 (Performance)
Version:	5.0.30-BK, 5.0.28	OS:	Linux (Linux, Mac OS X)
Assigned to:	Georgi Kodinov	CPU Architecture:	Any
Tags:	backport_050030SP1

Description:
The loose index scan optimization for GROUP BY with MIN or MAX does not work when used inside of other queries, such as CREATE TABLE...SELECT..., INSERT...SELECT..., or even as a subquery in the FROM clause.  The loose index scan method is mentioned at:

http://dev.mysql.com/doc/refman/5.0/en/loose-index-scan.html

For example, the following works and uses the optimization:

SELECT a, min(b) FROM tbl GROUP BY a;

These all do a full index scan instead of using the optimization, even though it is possible to use without additional work:

CREATE TABLE t1 SELECT a, min(b) FROM tbl GROUP BY a;
INSERT INTO t1 SELECT a, min(b) FROM tbl GROUP BY a;
SELECT * FROM (SELECT a, min(b) FROM tbl GROUP BY a) b;

You can tell what is happening if you have a large table (as it takes much longer) and also from viewing the handler status variables.

How to repeat:
The following SQL script will show the bad behavior:

CREATE TABLE bug_test (a INT, b INT, INDEX (a,b));
INSERT INTO bug_test (a, b) VALUES (1,1), (1,2), (1,3), (1,4), (1,5),(2, 2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6);

-- EXPLAIN shows it is working
EXPLAIN SELECT max(b), a FROM bug_test GROUP BY a;

-- use STATUS variables to see how it is really executed
FLUSH STATUS;
SELECT max(b), a FROM bug_test GROUP BY a;
-- Handler_read_first = 1   Handler_read_key = 8
SHOW STATUS LIKE 'handler_read%';

-- BAD queries
FLUSH STATUS;
CREATE TABLE t_2 SELECT max(b), a FROM bug_test GROUP BY a;
-- Handler_read_first = 1   Handler_read_next = 15
SHOW STATUS LIKE 'handler_read%';

FLUSH STATUS;
SELECT * FROM (SELECT max(b), a FROM bug_test GROUP BY a) b;
-- Handler_read_first = 1   Handler_read_next = 15   Handler_read_rnd_next = 5
SHOW STATUS LIKE 'handler_read%';

Suggested fix:
Allow the optimization to work when using the query as part of a bigger construct, when the bigger construct doesn't directly affect the query itself.

Thank you for a bug report. Verified just as described with 5.0.30-BK on Linux:

openxs@suse:~/dbs/5.0> 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 MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.30-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE bug_test (a INT, b INT, INDEX (a,b));
INSERT INTO bug_test (a, b) VALUES (1,1), (1,2), (1,3), (1,4), (1,5),(2, 2),
(2,3), (2,1), (3,1Query OK, 0 rows affected (0.22 sec)

mysql> INSERT INTO bug_test (a, b) VALUES (1,1), (1,2), (1,3), (1,4), (1,5),(2,
 2),
    -> (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6);
Query OK, 15 rows affected (0.01 sec)
Records: 15  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT max(b), a FROM bug_test GROUP BY a\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: bug_test
         type: range
possible_keys: NULL
          key: a
      key_len: 5
          ref: NULL
         rows: 8
        Extra: Using index for group-by
1 row in set (0.00 sec)

mysql> FLUSH STATUS;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT max(b), a FROM bug_test GROUP BY a;
+--------+------+
| max(b) | a    |
+--------+------+
|      5 |    1 |
|      3 |    2 |
|      1 |    3 |
|      6 |    4 |
+--------+------+
4 rows in set (0.00 sec)

mysql> SHOW STATUS LIKE 'handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 1     |
| Handler_read_key      | 8     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+
6 rows in set (0.00 sec)

mysql> FLUSH STATUS;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t_2 SELECT max(b), a FROM bug_test GROUP BY a;
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SHOW STATUS LIKE 'handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 1     |
| Handler_read_key      | 0     |
| Handler_read_next     | 15    |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+
6 rows in set (0.01 sec)

mysql> FLUSH STATUS;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM (SELECT max(b), a FROM bug_test GROUP BY a) b;
+--------+------+
| max(b) | a    |
+--------+------+
|      5 |    1 |
|      3 |    2 |
|      1 |    3 |
|      6 |    4 |
+--------+------+
4 rows in set (0.00 sec)

mysql> SHOW STATUS LIKE 'handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 1     |
| Handler_read_key      | 0     |
| Handler_read_next     | 15    |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 5     |
+-----------------------+-------+
6 rows in set (0.01 sec)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/15502

ChangeSet@1.2314, 2006-11-17 17:21:55+02:00, gkodinov@macbook.gmz +3 -0
  Bug#24156: Loose index scan not used with CREATE TABLE ...SELECT and similar
             statements
  Trying loose index scan is currently enabled only for top level SELECT 
  statements.
  Extend loose index scan applicability by :
   - trying over the current subselect (lex->current_select) instead of 
     the whole query (lex->select_lex) : enables loose index scan for 
     sub-queries.
   - allowing non-select statements with SELECT parts (like, e.g. 
     CREATE TABLE .. SELECT ...) to use loose index scan.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/15941

ChangeSet@1.2314, 2006-11-28 13:57:04+02:00, gkodinov@macbook.gmz +3 -0
  Bug#24156: Loose index scan not used with CREATE TABLE ...SELECT and similar
             statements
  Currently the optimizer evaluates loose index scan only for top-level SELECT
  statements
  Extend loose index scan applicability by :
   - Test the applicability of loose scan for each sub-select, instead of the
     whole query. This change enables loose index scan for sub-queries.
   - allow non-select statements with SELECT parts (like, e.g. 
     CREATE TABLE .. SELECT ...) to use loose index scan.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/15980

ChangeSet@1.2314, 2006-11-28 18:06:47+02:00, gkodinov@macbook.gmz +3 -0
  Bug#24156: Loose index scan not used with CREATE TABLE ...SELECT and similar
             statements
  Currently the optimizer evaluates loose index scan only for top-level SELECT
  statements
  Extend loose index scan applicability by :
   - Test the applicability of loose scan for each sub-select, instead of the
     whole query. This change enables loose index scan for sub-queries.
   - allow non-select statements with SELECT parts (like, e.g. 
     CREATE TABLE .. SELECT ...) to use loose index scan.

Pushed in 5.0.32/5.1.14-beta

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix for 5.0.32 and 5.1.14.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/17295

ChangeSet@1.2311, 2006-12-22 02:11:49+01:00, msvensson@neptunus.(none) +3 -0
  Bug#24156: Loose index scan not used with CREATE TABLE ...SELECT and similar
             statements
  Currently the optimizer evaluates loose index scan only for top-level SELECT
  statements
  Extend loose index scan applicability by :
   - Test the applicability of loose scan for each sub-select, instead of the
     whole query. This change enables loose index scan for sub-queries.
   - allow non-select statements with SELECT parts (like, e.g. 
     CREATE TABLE .. SELECT ...) to use loose index scan.