Bug #101805 Using index for group-by doesn't work with InnoDB compressed table
Submitted: 30 Nov 2020 16:16 Modified: 1 Jan 10:39
Reporter: Aftab Khan Email Updates:
Status: No Feedback Impact on me:
None 
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
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)
[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 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".