| Bug #61551 | Loose index scan is not used for select COUNT(distinct a) | ||
|---|---|---|---|
| Submitted: | 17 Jun 2011 12:26 | Modified: | 17 Jun 2011 12:26 |
| Reporter: | Valeriy Kravchuk | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.1.56 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[17 Jun 2011 12:28]
Valeriy Kravchuk
This is fixed in 5.5:
C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3312 test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.11 MySQL Community Server (GPL)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE TABLE `tt` (
-> `a` int(11) DEFAULT NULL,
-> `b` int(11) DEFAULT NULL,
-> `c` int(11) DEFAULT NULL,
->
-> KEY `a` (`a`)
-> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.19 sec)
mysql>
mysql> insert into tt values (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5);
Query OK, 5 rows affected (0.20 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> insert into tt select * from tt;
Query OK, 5 rows affected (0.02 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> insert into tt select * from tt;
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> insert into tt select * from tt;
Query OK, 20 rows affected (0.00 sec)
Records: 20 Duplicates: 0 Warnings: 0
mysql> insert into tt select * from tt;
Query OK, 40 rows affected (0.00 sec)
Records: 40 Duplicates: 0 Warnings: 0
mysql>
mysql> explain select distinct(a) from tt;
+----+-------------+-------+-------+---------------+------+---------+------+----
--+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | row
s | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+----
--+--------------------------+
| 1 | SIMPLE | tt | range | NULL | a | 5 | NULL |
9 | Using index for group-by |
+----+-------------+-------+-------+---------------+------+---------+------+----
--+--------------------------+
1 row in set (0.01 sec)
mysql> explain select count(distinct a) from tt;
+----+-------------+-------+-------+---------------+------+---------+------+----
--+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | row
s | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+----
--+--------------------------+
| 1 | SIMPLE | tt | range | NULL | a | 5 | NULL |
9 | Using index for group-by |
+----+-------------+-------+-------+---------------+------+---------+------+----
--+--------------------------+
1 row in set (0.05 sec)
Description: With the following table: mysql> show create table tt\G *************************** 1. row *************************** Table: tt Create Table: CREATE TABLE `tt` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` int(11) DEFAULT NULL, `e` int(11) DEFAULT NULL, KEY `a` (`a`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 1 row in set (0.00 sec) We have: mysql> explain select distinct(a) from tt; +----+-------------+-------+-------+---------------+------+---------+------+---- --+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | row s | Extra | +----+-------------+-------+-------+---------------+------+---------+------+---- --+--------------------------+ | 1 | SIMPLE | tt | range | NULL | a | 5 | NULL | 9 | Using index for group-by | +----+-------------+-------+-------+---------------+------+---------+------+---- --+--------------------------+ 1 row in set (0.03 sec) But: mysql> explain select count(distinct a) from tt; +----+-------------+-------+-------+---------------+------+---------+------+---- --+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | row s | Extra | +----+-------------+-------+-------+---------------+------+---------+------+---- --+-------------+ | 1 | SIMPLE | tt | index | NULL | a | 5 | NULL | 8 0 | Using index | +----+-------------+-------+-------+---------------+------+---------+------+---- --+-------------+ 1 row in set (0.03 sec) Workaround is: mysql> explain select count(*) from (select distinct a from tt) x; +----+-------------+-------+-------+---------------+------+---------+------+---- --+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | row s | Extra | +----+-------------+-------+-------+---------------+------+---------+------+---- --+------------------------------+ | 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NUL L | Select tables optimized away | | 2 | DERIVED | tt | range | NULL | a | 5 | NULL | 9 | Using index for group-by | +----+-------------+-------+-------+---------------+------+---------+------+---- --+------------------------------+ 2 rows in set (0.01 sec) How to repeat: CREATE TABLE `tt` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, `c` int(11) DEFAULT NULL, KEY `a` (`a`) ) ENGINE=MyISAM; insert into tt values (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5); insert into tt select * from tt; insert into tt select * from tt; insert into tt select * from tt; insert into tt select * from tt; explain select distinct(a) from tt; explain select count(distinct a) from tt; Suggested fix: Use loose index scan for this case also?