| Bug #101805 | Using index for group-by doesn't work with InnoDB compressed table | ||
|---|---|---|---|
| Submitted: | 30 Nov 2020 16:16 | Modified: | 1 Jan 2021 10:39 |
| Reporter: | Aftab Khan | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 5.7.32 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[30 Nov 2020 16:16]
Aftab Khan
sample data
Attachment: bar.sql (application/octet-stream, text), 4.95 KiB.
[1 Dec 2020 10:39]
MySQL Verification Team
Hello Aftab Khan,
Thank you for the report and test case.
However I'm not seeing much difference in my tests using your test case. Am I missing anything here? Please let me know.
-
--
mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| innodb_version | 5.7.32 |
+-------------------------+------------------------------+
mysql> -- Create innodb compressed table --
mysql>
mysql> CREATE TABLE `bar` (
-> `id` varchar(32) NOT NULL,
-> KEY `id` (`id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
-> ;
Query OK, 0 rows affected (0.23 sec)
mysql> source /tmp/bar.sql;
Query OK, 0 rows affected (0.00 sec)
.
Query OK, 0 rows affected (0.00 sec)
mysql> analyze table bar;
mysql> optimize table bar;
mysql> explain select distinct id from bar where id in ('0b69bb61bfeb120b6514b65ba35cfd06','7819856b0b8d375f7af383c6364df8f6','2c80aaf6642eaaa35bb65db0c479842d','7215eb53e29b61dc089f908b7ae2a8ac','9ecae8b4676a8e48a21b92a884251e70','5d263eef3b6a4072d782aa1eaba4b2a5','c80868421c90dbcf5e05b88ad6dedc5d','8110563fcb44c4f2b2d5fe2c335c7a5c','b2fb6592cc87b1acdcc667d6f5fbac59','a484b1fafb484eea6a44f72758f3d3f7','ba33780b81dec48f7a097fa0ef911813','647c49c72606d660577220182ccbe40b','ae8ccc5c5f9513f6f95c98bbaab39ed8','84993ee038f232e291d01ce127114686','90a7354028df71c8e925bb2ecd16b740','a522ec5365b028d78c71f7965f19b9ac','c2b71b8b02de98a4525f0a477f425272','c95dfddc6ed44a8713890aa6c18ae5c5','fd3df6086b3afc156734fbf1820ee79c','46757f937e92fea74a45718e96790182','c9fd0d9cd1dcdc7a07fa707177af513d','501dd6257a9053be812e14281792cee1') limit 5\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: bar
partitions: NULL
type: range
possible_keys: id
key: id
key_len: 98
ref: NULL
rows: 22
filtered: 100.00
Extra: Using where; Using index; Using temporary
1 row in set, 1 warning (0.00 sec)
mysql> -- Create uncompressed table ---
mysql> drop table bar;
mysql> CREATE TABLE `bar` (
-> `id` varchar(32) NOT NULL,
-> KEY `id` (`id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
mysql> source /tmp/bar.sql;
Query OK, 0 rows affected (0.00 sec)
.
Query OK, 0 rows affected (0.00 sec)
mysql> analyze table bar;
+----------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------+---------+----------+----------+
| test.bar | analyze | status | OK |
+----------+---------+----------+----------+
mysql> optimize table bar;
mysql> explain select distinct id from bar where id in ('0b69bb61bfeb120b6514b65ba35cfd06','7819856b0b8d375f7af383c6364df8f6','2c80aaf6642eaaa35bb65db0c479842d','7215eb53e29b61dc089f908b7ae2a8ac','9ecae8b4676a8e48a21b92a884251e70','5d263eef3b6a4072d782aa1eaba4b2a5','c80868421c90dbcf5e05b88ad6dedc5d','8110563fcb44c4f2b2d5fe2c335c7a5c','b2fb6592cc87b1acdcc667d6f5fbac59','a484b1fafb484eea6a44f72758f3d3f7','ba33780b81dec48f7a097fa0ef911813','647c49c72606d660577220182ccbe40b','ae8ccc5c5f9513f6f95c98bbaab39ed8','84993ee038f232e291d01ce127114686','90a7354028df71c8e925bb2ecd16b740','a522ec5365b028d78c71f7965f19b9ac','c2b71b8b02de98a4525f0a477f425272','c95dfddc6ed44a8713890aa6c18ae5c5','fd3df6086b3afc156734fbf1820ee79c','46757f937e92fea74a45718e96790182','c9fd0d9cd1dcdc7a07fa707177af513d','501dd6257a9053be812e14281792cee1') limit 5\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: bar
partitions: NULL
type: range
possible_keys: id
key: id
key_len: 98
ref: NULL
rows: 22
filtered: 100.00
Extra: Using where; Using index; Using temporary
1 row in set, 1 warning (0.00 sec)
mysql> explain select distinct id from bar where id in ('0b69bb61bfeb120b6514b65ba35cfd06','7819856b0b8d375f7af383c6364df8f6','2c80aaf6642eaaa35bb65db0c479842d','7215eb53e29b61dc089f908b7ae2a8ac','9ecae8b4676a8e48a21b92a884251e70','5d263eef3b6a4072d782aa1eaba4b2a5','c80868421c90dbcf5e05b88ad6dedc5d','8110563fcb44c4f2b2d5fe2c335c7a5c','b2fb6592cc87b1acdcc667d6f5fbac59','a484b1fafb484eea6a44f72758f3d3f7','ba33780b81dec48f7a097fa0ef911813','647c49c72606d660577220182ccbe40b','ae8ccc5c5f9513f6f95c98bbaab39ed8','84993ee038f232e291d01ce127114686','90a7354028df71c8e925bb2ecd16b740','a522ec5365b028d78c71f7965f19b9ac','c2b71b8b02de98a4525f0a477f425272','c95dfddc6ed44a8713890aa6c18ae5c5','fd3df6086b3afc156734fbf1820ee79c','46757f937e92fea74a45718e96790182','c9fd0d9cd1dcdc7a07fa707177af513d','501dd6257a9053be812e14281792cee1') \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: bar
partitions: NULL
type: range
possible_keys: id
key: id
key_len: 98
ref: NULL
rows: 22
filtered: 100.00
Extra: Using where; Using index
1 row in set, 1 warning (0.05 sec)
regards,
Umesh
[2 Jan 2021 1:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
Description: Loose index scan is not applicable to a query using innodb compressed table. How to repeat: -- Create innodb compressed table -- CREATE TABLE `bar` ( `id` varchar(32) NOT NULL, KEY `id` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 ; -- load sample data -- source bar.sql -- query plan -- explain select distinct id from bar where id in ('0b69bb61bfeb120b6514b65ba35cfd06','7819856b0b8d375f7af383c6364df8f6','2c80aaf6642eaaa35bb65db0c479842d','7215eb53e29b61dc089f908b7ae2a8ac','9ecae8b4676a8e48a21b92a884251e70','5d263eef3b6a4072d782aa1eaba4b2a5','c80868421c90dbcf5e05b88ad6dedc5d','8110563fcb44c4f2b2d5fe2c335c7a5c','b2fb6592cc87b1acdcc667d6f5fbac59','a484b1fafb484eea6a44f72758f3d3f7','ba33780b81dec48f7a097fa0ef911813','647c49c72606d660577220182ccbe40b','ae8ccc5c5f9513f6f95c98bbaab39ed8','84993ee038f232e291d01ce127114686','90a7354028df71c8e925bb2ecd16b740','a522ec5365b028d78c71f7965f19b9ac','c2b71b8b02de98a4525f0a477f425272','c95dfddc6ed44a8713890aa6c18ae5c5','fd3df6086b3afc156734fbf1820ee79c','46757f937e92fea74a45718e96790182','c9fd0d9cd1dcdc7a07fa707177af513d','501dd6257a9053be812e14281792cee1') limit 5\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: bar partitions: NULL type: range possible_keys: id key: id key_len: 98 ref: NULL rows: 22 filtered: 100.00 Extra: Using where; Using index; Using temporary 1 row in set, 1 warning (0.00 sec) --- Create compressed table --- drop table bar; CREATE TABLE `bar` ( `id` varchar(32) NOT NULL, KEY `id` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; --- load sample data --- source bar.sql explain select distinct id from bar where id in ('0b69bb61bfeb120b6514b65ba35cfd06','7819856b0b8d375f7af383c6364df8f6','2c80aaf6642eaaa35bb65db0c479842d','7215eb53e29b61dc089f908b7ae2a8ac','9ecae8b4676a8e48a21b92a884251e70','5d263eef3b6a4072d782aa1eaba4b2a5','c80868421c90dbcf5e05b88ad6dedc5d','8110563fcb44c4f2b2d5fe2c335c7a5c','b2fb6592cc87b1acdcc667d6f5fbac59','a484b1fafb484eea6a44f72758f3d3f7','ba33780b81dec48f7a097fa0ef911813','647c49c72606d660577220182ccbe40b','ae8ccc5c5f9513f6f95c98bbaab39ed8','84993ee038f232e291d01ce127114686','90a7354028df71c8e925bb2ecd16b740','a522ec5365b028d78c71f7965f19b9ac','c2b71b8b02de98a4525f0a477f425272','c95dfddc6ed44a8713890aa6c18ae5c5','fd3df6086b3afc156734fbf1820ee79c','46757f937e92fea74a45718e96790182','c9fd0d9cd1dcdc7a07fa707177af513d','501dd6257a9053be812e14281792cee1') limit 5\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: bar partitions: NULL type: range possible_keys: id key: id key_len: 98 ref: NULL rows: 3 filtered: 100.00 Extra: Using where; Using index for group-by; Using temporary 1 row in set, 1 warning (0.00 sec) Moreover, after removing LIMIT clause MySQL is able to avoid creation of temporary table e.g. explain select distinct id from bar where id in ('0b69bb61bfeb120b6514b65ba35cfd06','7819856b0b8d375f7af383c6364df8f6','2c80aaf6642eaaa35bb65db0c479842d','7215eb53e29b61dc089f908b7ae2a8ac','9ecae8b4676a8e48a21b92a884251e70','5d263eef3b6a4072d782aa1eaba4b2a5','c80868421c90dbcf5e05b88ad6dedc5d','8110563fcb44c4f2b2d5fe2c335c7a5c','b2fb6592cc87b1acdcc667d6f5fbac59','a484b1fafb484eea6a44f72758f3d3f7','ba33780b81dec48f7a097fa0ef911813','647c49c72606d660577220182ccbe40b','ae8ccc5c5f9513f6f95c98bbaab39ed8','84993ee038f232e291d01ce127114686','90a7354028df71c8e925bb2ecd16b740','a522ec5365b028d78c71f7965f19b9ac','c2b71b8b02de98a4525f0a477f425272','c95dfddc6ed44a8713890aa6c18ae5c5','fd3df6086b3afc156734fbf1820ee79c','46757f937e92fea74a45718e96790182','c9fd0d9cd1dcdc7a07fa707177af513d','501dd6257a9053be812e14281792cee1') \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: bar partitions: NULL type: range possible_keys: id key: id key_len: 98 ref: NULL rows: 3 filtered: 100.00 Extra: Using where; Using index for group-by <<<< 1 row in set, 1 warning (0.00 sec)