| Bug #77499 | Incorrect Partition Pruning with Subpartitions | ||
|---|---|---|---|
| Submitted: | 26 Jun 2015 8:30 | Modified: | 23 Nov 2015 4:57 |
| Reporter: | Jervin R | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Partitions | Severity: | S3 (Non-critical) |
| Version: | 5.6.25, 5.6.26, 5.7.8 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[27 Jun 2015 6:52]
MySQL Verification Team
Hello Jervin R, Thank you for the report and test case. Observed this with 5.6.27, 5.7.8 builds. Thanks, Umesh
[27 Jun 2015 6:53]
MySQL Verification Team
// 5.6.27 [umshastr@hod03]/export/umesh/server/binaries/mysql-5.6.27: bin/mysql -uroot -S /tmp/mysql_ushastry.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.6.27-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial) Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create database bug77499; Query OK, 1 row affected (0.00 sec) mysql> use bug77499 Database changed mysql> create table t (id int unsigned not null auto_increment, d date not null, c char(32) not null, primary key (id, d, c))engine=innodb partition by range(to_days(d)) subpartition by key (c) subpartitions 3 ( partition Mar2015 values less than (736054), partition Jun2015 values less than (736145), partition Sep2015 values less than (736237)); Query OK, 0 rows affected (0.01 sec) mysql> insert into t (d, c) values ('2015-01-01', '501bc380461e9c91c3ba3c3d9603f087'), ('2015-04-01', '501bc380461e9c91c3ba3c3d9603f087'), ('2015-07-01', '501bc380461e9c91c3ba3c3d9603f087'), ('2015-02-01', '00e25fdb97b4e84f01beabd5850c4a49'), ('2015-05-01', '00e25fdb97b4e84f01beabd5850c4a49'), ('2015-08-01', '00e25fdb97b4e84f01beabd5850c4a49'), ('2015-03-01', '04d7e5edfa5c117fab259bcdb3a6bf8a'), ('2015-05-01', '04d7e5edfa5c117fab259bcdb3a6bf8a'), ('2015-09-01', '04d7e5edfa5c117fab259bcdb3a6bf8a'); Query OK, 9 rows affected (0.00 sec) Records: 9 Duplicates: 0 Warnings: 0 mysql> insert into t (d, c) values ('2015-01-01', '144aa312a044aa1f6823c88be92afc8f'), ('2015-04-01', '144aa312a044aa1f6823c88be92afc8f'), ('2015-07-01', '144aa312a044aa1f6823c88be92afc8f'), ('2015-02-01', 'da02ce650cd9f2496e9447bf2c51c20f'), ('2015-05-01', 'da02ce650cd9f2496e9447bf2c51c20f'), ('2015-08-01', 'da02ce650cd9f2496e9447bf2c51c20f'), ('2015-03-01', 'acc96601808442120a3b840d1a6d8fb3'), ('2015-06-01', 'acc96601808442120a3b840d1a6d8fb3'), ('2015-09-01', 'acc96601808442120a3b840d1a6d8fb3'); Query OK, 9 rows affected (0.00 sec) Records: 9 Duplicates: 0 Warnings: 0 mysql> insert into t (d, c) values ('2015-02-01', '501bc380461e9c91c3ba3c3d9603f087'), ('2015-05-01', '501bc380461e9c91c3ba3c3d9603f087'), ('2015-08-01', '501bc380461e9c91c3ba3c3d9603f087'), ('2015-03-01', '00e25fdb97b4e84f01beabd5850c4a49'), ('2015-07-01', '00e25fdb97b4e84f01beabd5850c4a49'), ('2015-09-01', '00e25fdb97b4e84f01beabd5850c4a49'), ('2015-01-01', '04d7e5edfa5c117fab259bcdb3a6bf8a'), ('2015-04-01', '04d7e5edfa5c117fab259bcdb3a6bf8a'), ('2015-07-01', '04d7e5edfa5c117fab259bcdb3a6bf8a'); Query OK, 9 rows affected (0.00 sec) Records: 9 Duplicates: 0 Warnings: 0 mysql> alter table t add column n int unsigned not null default 0; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table t add key (c, n), add key (d, n); Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> update t set n = id; Query OK, 27 rows affected (0.00 sec) Rows matched: 27 Changed: 27 Warnings: 0 mysql> select * from t order by c, d; +----+------------+----------------------------------+----+ | id | d | c | n | +----+------------+----------------------------------+----+ | 4 | 2015-02-01 | 00e25fdb97b4e84f01beabd5850c4a49 | 4 | | 22 | 2015-03-01 | 00e25fdb97b4e84f01beabd5850c4a49 | 22 | | 5 | 2015-05-01 | 00e25fdb97b4e84f01beabd5850c4a49 | 5 | | 23 | 2015-07-01 | 00e25fdb97b4e84f01beabd5850c4a49 | 23 | | 6 | 2015-08-01 | 00e25fdb97b4e84f01beabd5850c4a49 | 6 | | 24 | 2015-09-01 | 00e25fdb97b4e84f01beabd5850c4a49 | 24 | | 25 | 2015-01-01 | 04d7e5edfa5c117fab259bcdb3a6bf8a | 25 | | 7 | 2015-03-01 | 04d7e5edfa5c117fab259bcdb3a6bf8a | 7 | | 26 | 2015-04-01 | 04d7e5edfa5c117fab259bcdb3a6bf8a | 26 | | 8 | 2015-05-01 | 04d7e5edfa5c117fab259bcdb3a6bf8a | 8 | | 27 | 2015-07-01 | 04d7e5edfa5c117fab259bcdb3a6bf8a | 27 | | 9 | 2015-09-01 | 04d7e5edfa5c117fab259bcdb3a6bf8a | 9 | | 10 | 2015-01-01 | 144aa312a044aa1f6823c88be92afc8f | 10 | | 11 | 2015-04-01 | 144aa312a044aa1f6823c88be92afc8f | 11 | | 12 | 2015-07-01 | 144aa312a044aa1f6823c88be92afc8f | 12 | | 1 | 2015-01-01 | 501bc380461e9c91c3ba3c3d9603f087 | 1 | | 19 | 2015-02-01 | 501bc380461e9c91c3ba3c3d9603f087 | 19 | | 2 | 2015-04-01 | 501bc380461e9c91c3ba3c3d9603f087 | 2 | | 20 | 2015-05-01 | 501bc380461e9c91c3ba3c3d9603f087 | 20 | | 3 | 2015-07-01 | 501bc380461e9c91c3ba3c3d9603f087 | 3 | | 21 | 2015-08-01 | 501bc380461e9c91c3ba3c3d9603f087 | 21 | | 16 | 2015-03-01 | acc96601808442120a3b840d1a6d8fb3 | 16 | | 17 | 2015-06-01 | acc96601808442120a3b840d1a6d8fb3 | 17 | | 18 | 2015-09-01 | acc96601808442120a3b840d1a6d8fb3 | 18 | | 13 | 2015-02-01 | da02ce650cd9f2496e9447bf2c51c20f | 13 | | 14 | 2015-05-01 | da02ce650cd9f2496e9447bf2c51c20f | 14 | | 15 | 2015-08-01 | da02ce650cd9f2496e9447bf2c51c20f | 15 | +----+------------+----------------------------------+----+ 27 rows in set (0.00 sec) mysql> explain partitions select * from t where c = '04d7e5edfa5c117fab259bcdb3a6bf8a' and d between '2015-04-01' and '2015-06-01' \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t partitions: Mar2015_Mar2015sp0,Jun2015_Jun2015sp0 type: ref possible_keys: c,d key: c key_len: 32 ref: const rows: 4 Extra: Using where; Using index 1 row in set (0.00 sec) mysql> explain partitions select * from t where c = '04d7e5edfa5c117fab259bcdb3a6bf8a' and d between '2015-04-31' and '2015-06-01' \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t partitions: Mar2015_Mar2015sp0,Jun2015_Jun2015sp0 type: ref possible_keys: c,d key: c key_len: 32 ref: const rows: 4 Extra: Using where; Using index 1 row in set (0.00 sec) mysql> explain partitions select * from t where c = '04d7e5edfa5c117fab259bcdb3a6bf8a' and d between '2015-05-01' and '2015-06-01' \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t partitions: Mar2015_Mar2015sp0,Jun2015_Jun2015sp0 type: ref possible_keys: c,d key: c key_len: 32 ref: const rows: 4 Extra: Using where; Using index 1 row in set (0.00 sec) mysql> explain partitions select * from t where c = '04d7e5edfa5c117fab259bcdb3a6bf8a' and d between '2015-06-01' and '2015-06-01' \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t partitions: Jun2015_Jun2015sp0 type: ref possible_keys: c,d key: d key_len: 3 ref: const rows: 1 Extra: Using where; Using index 1 row in set (0.00 sec)
[27 Jun 2015 6:53]
MySQL Verification Team
[umshastr@hod03]/export/umesh/server/binaries/mysql-5.7.8: bin/mysql -uroot -S /tmp/mysql_ushastry.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.8-rc-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial) Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> mysql> mysql> create database bug77499; Query OK, 1 row affected (0.00 sec) mysql> use bug77499; Database changed mysql> create table t (id int unsigned not null auto_increment, d date not null, c char(32) not null, primary key (id, d, c))engine=innodb partition by range(to_days(d)) subpartition by key (c) subpartitions 3 ( partition Mar2015 values less than (736054), partition Jun2015 values less than (736145), partition Sep2015 values less than (736237)); Query OK, 0 rows affected (0.01 sec) mysql> insert into t (d, c) values ('2015-01-01', '501bc380461e9c91c3ba3c3d9603f087'), ('2015-04-01', '501bc380461e9c91c3ba3c3d9603f087'), ('2015-07-01', '501bc380461e9c91c3ba3c3d9603f087'), ('2015-02-01', '00e25fdb97b4e84f01beabd5850c4a49'), ('2015-05-01', '00e25fdb97b4e84f01beabd5850c4a49'), ('2015-08-01', '00e25fdb97b4e84f01beabd5850c4a49'), ('2015-03-01', '04d7e5edfa5c117fab259bcdb3a6bf8a'), ('2015-05-01', '04d7e5edfa5c117fab259bcdb3a6bf8a'), ('2015-09-01', '04d7e5edfa5c117fab259bcdb3a6bf8a'); Query OK, 9 rows affected (0.01 sec) Records: 9 Duplicates: 0 Warnings: 0 mysql> insert into t (d, c) values ('2015-01-01', '144aa312a044aa1f6823c88be92afc8f'), ('2015-04-01', '144aa312a044aa1f6823c88be92afc8f'), ('2015-07-01', '144aa312a044aa1f6823c88be92afc8f'), ('2015-02-01', 'da02ce650cd9f2496e9447bf2c51c20f'), ('2015-05-01', 'da02ce650cd9f2496e9447bf2c51c20f'), ('2015-08-01', 'da02ce650cd9f2496e9447bf2c51c20f'), ('2015-03-01', 'acc96601808442120a3b840d1a6d8fb3'), ('2015-06-01', 'acc96601808442120a3b840d1a6d8fb3'), ('2015-09-01', 'acc96601808442120a3b840d1a6d8fb3'); Query OK, 9 rows affected (0.00 sec) Records: 9 Duplicates: 0 Warnings: 0 mysql> insert into t (d, c) values ('2015-02-01', '501bc380461e9c91c3ba3c3d9603f087'), ('2015-05-01', '501bc380461e9c91c3ba3c3d9603f087'), ('2015-08-01', '501bc380461e9c91c3ba3c3d9603f087'), ('2015-03-01', '00e25fdb97b4e84f01beabd5850c4a49'), ('2015-07-01', '00e25fdb97b4e84f01beabd5850c4a49'), ('2015-09-01', '00e25fdb97b4e84f01beabd5850c4a49'), ('2015-01-01', '04d7e5edfa5c117fab259bcdb3a6bf8a'), ('2015-04-01', '04d7e5edfa5c117fab259bcdb3a6bf8a'), ('2015-07-01', '04d7e5edfa5c117fab259bcdb3a6bf8a'); Query OK, 9 rows affected (0.00 sec) Records: 9 Duplicates: 0 Warnings: 0 mysql> alter table t add column n int unsigned not null default 0; Query OK, 0 rows affected (0.24 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table t add key (c, n), add key (d, n); Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> update t set n = id; Query OK, 27 rows affected (0.00 sec) Rows matched: 27 Changed: 27 Warnings: 0 mysql> select * from t order by c, d; +----+------------+----------------------------------+----+ | id | d | c | n | +----+------------+----------------------------------+----+ | 4 | 2015-02-01 | 00e25fdb97b4e84f01beabd5850c4a49 | 4 | | 22 | 2015-03-01 | 00e25fdb97b4e84f01beabd5850c4a49 | 22 | | 5 | 2015-05-01 | 00e25fdb97b4e84f01beabd5850c4a49 | 5 | | 23 | 2015-07-01 | 00e25fdb97b4e84f01beabd5850c4a49 | 23 | | 6 | 2015-08-01 | 00e25fdb97b4e84f01beabd5850c4a49 | 6 | | 24 | 2015-09-01 | 00e25fdb97b4e84f01beabd5850c4a49 | 24 | | 25 | 2015-01-01 | 04d7e5edfa5c117fab259bcdb3a6bf8a | 25 | | 7 | 2015-03-01 | 04d7e5edfa5c117fab259bcdb3a6bf8a | 7 | | 26 | 2015-04-01 | 04d7e5edfa5c117fab259bcdb3a6bf8a | 26 | | 8 | 2015-05-01 | 04d7e5edfa5c117fab259bcdb3a6bf8a | 8 | | 27 | 2015-07-01 | 04d7e5edfa5c117fab259bcdb3a6bf8a | 27 | | 9 | 2015-09-01 | 04d7e5edfa5c117fab259bcdb3a6bf8a | 9 | | 10 | 2015-01-01 | 144aa312a044aa1f6823c88be92afc8f | 10 | | 11 | 2015-04-01 | 144aa312a044aa1f6823c88be92afc8f | 11 | | 12 | 2015-07-01 | 144aa312a044aa1f6823c88be92afc8f | 12 | | 1 | 2015-01-01 | 501bc380461e9c91c3ba3c3d9603f087 | 1 | | 19 | 2015-02-01 | 501bc380461e9c91c3ba3c3d9603f087 | 19 | | 2 | 2015-04-01 | 501bc380461e9c91c3ba3c3d9603f087 | 2 | | 20 | 2015-05-01 | 501bc380461e9c91c3ba3c3d9603f087 | 20 | | 3 | 2015-07-01 | 501bc380461e9c91c3ba3c3d9603f087 | 3 | | 21 | 2015-08-01 | 501bc380461e9c91c3ba3c3d9603f087 | 21 | | 16 | 2015-03-01 | acc96601808442120a3b840d1a6d8fb3 | 16 | | 17 | 2015-06-01 | acc96601808442120a3b840d1a6d8fb3 | 17 | | 18 | 2015-09-01 | acc96601808442120a3b840d1a6d8fb3 | 18 | | 13 | 2015-02-01 | da02ce650cd9f2496e9447bf2c51c20f | 13 | | 14 | 2015-05-01 | da02ce650cd9f2496e9447bf2c51c20f | 14 | | 15 | 2015-08-01 | da02ce650cd9f2496e9447bf2c51c20f | 15 | +----+------------+----------------------------------+----+ 27 rows in set (0.01 sec) mysql> explain partitions select * from t where c = '04d7e5edfa5c117fab259bcdb3a6bf8a' and d between '2015-04-01' and '2015-06-01' \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t partitions: Mar2015_Mar2015sp0,Jun2015_Jun2015sp0 type: ref possible_keys: c,d key: c key_len: 32 ref: const rows: 4 filtered: 42.86 Extra: Using where; Using index 1 row in set, 2 warnings (0.00 sec) mysql> explain partitions select * from t where c = '04d7e5edfa5c117fab259bcdb3a6bf8a' and d between '2015-04-31' and '2015-06-01' \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t partitions: Mar2015_Mar2015sp0,Jun2015_Jun2015sp0 type: range possible_keys: c,d key: d key_len: 3 ref: NULL rows: 2 filtered: 57.14 Extra: Using where; Using index 1 row in set, 2 warnings (0.00 sec) mysql> explain partitions select * from t where c = '04d7e5edfa5c117fab259bcdb3a6bf8a' and d between '2015-05-01' and '2015-06-01' \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t partitions: Mar2015_Mar2015sp0,Jun2015_Jun2015sp0 type: range possible_keys: c,d key: d key_len: 3 ref: NULL rows: 2 filtered: 57.14 Extra: Using where; Using index 1 row in set, 2 warnings (0.00 sec) mysql> explain partitions select * from t where c = '04d7e5edfa5c117fab259bcdb3a6bf8a' and d between '2015-06-01' and '2015-06-01' \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t partitions: Jun2015_Jun2015sp0 type: ref possible_keys: c,d key: d key_len: 3 ref: const rows: 1 filtered: 66.67 Extra: Using where; Using index 1 row in set, 2 warnings (0.00 sec)
[23 Nov 2015 4:55]
ADITYA ANANTHAPADMANABHA
Duplicate of http://bugs.mysql.com/bug.php?id=49754 which was closed with the documentation stating that irrespective of the range ,the first partition is always included for the scans since TO_DAYS() can return return NULL for invalid dates and NULL values are always stored in first partition. For some queries which is mentioned in Bug page select * from t where c = '04d7e5edfa5c117fab259bcdb3a6bf8a' and d between '2015-06-01' and '2015-06-01' ; There is a small optimization made to remove scanning of the partitions containing NULL,if the dates are in same month and year and are valid. Workaround 1 ------------ If performance is the worry we can create a partition to hold all NULL values (like '... LESS THAN (0)'). Though this is included in range queries ,it will be a empty and not cause performance hit during scans Workaround 2 ------------------- We have the option to create partitions using directly date columns rather than to_days() create table t (id int unsigned not null auto_increment, d date not null, c char(32) not null, primary key (id, d, c)) engine=innodb partition by range columns (d) subpartition by key (c) subpartitions 3 ( partition Mar2015 value less than ('2015-04-01'), partition Jun2015 values less than ('2015-07-01'), partition Sep2015 values less than ('2015-10-01'));
Description: Partitioning by RANGE on date, subpartition by key does not result in expected pruning. See test case below, first 3 tests should not include Mar2015_Mar2015sp0 in partitions list. How to repeat: mysql [localhost] {msandbox} (test) > create table t (id int unsigned not null auto_increment, d date not null, c char(32) not null, primary key (id, d, c))engine=innodb partition by range(to_days(d)) subpartition by key (c) subpartitions 3 ( partition Mar2015 values less than (736054), partition Jun2015 values less than (736145), partition Sep2015 values less than (736237)); Query OK, 0 rows affected (0.03 sec) mysql [localhost] {msandbox} (test) > insert into t (d, c) values ('2015-01-01', '501bc380461e9c91c3ba3c3d9603f087'), ('2015-04-01', '501bc380461e9c91c3ba3c3d9603f087'), ('2015-07-01', '501bc380461e9c91c3ba3c3d9603f087'), ('2015-02-01', '00e25fdb97b4e84f01beabd5850c4a49'), ('2015-05-01', '00e25fdb97b4e84f01beabd5850c4a49'), ('2015-08-01', '00e25fdb97b4e84f01beabd5850c4a49'), ('2015-03-01', '04d7e5edfa5c117fab259bcdb3a6bf8a'), ('2015-05-01', '04d7e5edfa5c117fab259bcdb3a6bf8a'), ('2015-09-01', '04d7e5edfa5c117fab259bcdb3a6bf8a'); Query OK, 9 rows affected (0.00 sec) Records: 9 Duplicates: 0 Warnings: 0 mysql [localhost] {msandbox} (test) > insert into t (d, c) values ('2015-01-01', '144aa312a044aa1f6823c88be92afc8f'), ('2015-04-01', '144aa312a044aa1f6823c88be92afc8f'), ('2015-07-01', '144aa312a044aa1f6823c88be92afc8f'), ('2015-02-01', 'da02ce650cd9f2496e9447bf2c51c20f'), ('2015-05-01', 'da02ce650cd9f2496e9447bf2c51c20f'), ('2015-08-01', 'da02ce650cd9f2496e9447bf2c51c20f'), ('2015-03-01', 'acc96601808442120a3b840d1a6d8fb3'), ('2015-06-01', 'acc96601808442120a3b840d1a6d8fb3'), ('2015-09-01', 'acc96601808442120a3b840d1a6d8fb3'); Query OK, 9 rows affected (0.00 sec) Records: 9 Duplicates: 0 Warnings: 0 mysql [localhost] {msandbox} (test) > insert into t (d, c) values ('2015-02-01', '501bc380461e9c91c3ba3c3d9603f087'), ('2015-05-01', '501bc380461e9c91c3ba3c3d9603f087'), ('2015-08-01', '501bc380461e9c91c3ba3c3d9603f087'), ('2015-03-01', '00e25fdb97b4e84f01beabd5850c4a49'), ('2015-07-01', '00e25fdb97b4e84f01beabd5850c4a49'), ('2015-09-01', '00e25fdb97b4e84f01beabd5850c4a49'), ('2015-01-01', '04d7e5edfa5c117fab259bcdb3a6bf8a'), ('2015-04-01', '04d7e5edfa5c117fab259bcdb3a6bf8a'), ('2015-07-01', '04d7e5edfa5c117fab259bcdb3a6bf8a'); Query OK, 9 rows affected (0.00 sec) Records: 9 Duplicates: 0 Warnings: 0 mysql [localhost] {msandbox} (test) > alter table t add column n int unsigned not null default 0; Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql [localhost] {msandbox} (test) > alter table t add key (c, n), add key (d, n); Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql [localhost] {msandbox} (test) > update t set n = id; Query OK, 27 rows affected (0.01 sec) Rows matched: 27 Changed: 27 Warnings: 0 mysql [localhost] {msandbox} (test) > select * from t order by c, d; +----+------------+----------------------------------+----+ | id | d | c | n | +----+------------+----------------------------------+----+ | 4 | 2015-02-01 | 00e25fdb97b4e84f01beabd5850c4a49 | 4 | | 22 | 2015-03-01 | 00e25fdb97b4e84f01beabd5850c4a49 | 22 | | 5 | 2015-05-01 | 00e25fdb97b4e84f01beabd5850c4a49 | 5 | | 23 | 2015-07-01 | 00e25fdb97b4e84f01beabd5850c4a49 | 23 | | 6 | 2015-08-01 | 00e25fdb97b4e84f01beabd5850c4a49 | 6 | | 24 | 2015-09-01 | 00e25fdb97b4e84f01beabd5850c4a49 | 24 | | 25 | 2015-01-01 | 04d7e5edfa5c117fab259bcdb3a6bf8a | 25 | | 7 | 2015-03-01 | 04d7e5edfa5c117fab259bcdb3a6bf8a | 7 | | 26 | 2015-04-01 | 04d7e5edfa5c117fab259bcdb3a6bf8a | 26 | | 8 | 2015-05-01 | 04d7e5edfa5c117fab259bcdb3a6bf8a | 8 | | 27 | 2015-07-01 | 04d7e5edfa5c117fab259bcdb3a6bf8a | 27 | | 9 | 2015-09-01 | 04d7e5edfa5c117fab259bcdb3a6bf8a | 9 | | 10 | 2015-01-01 | 144aa312a044aa1f6823c88be92afc8f | 10 | | 11 | 2015-04-01 | 144aa312a044aa1f6823c88be92afc8f | 11 | | 12 | 2015-07-01 | 144aa312a044aa1f6823c88be92afc8f | 12 | | 1 | 2015-01-01 | 501bc380461e9c91c3ba3c3d9603f087 | 1 | | 19 | 2015-02-01 | 501bc380461e9c91c3ba3c3d9603f087 | 19 | | 2 | 2015-04-01 | 501bc380461e9c91c3ba3c3d9603f087 | 2 | | 20 | 2015-05-01 | 501bc380461e9c91c3ba3c3d9603f087 | 20 | | 3 | 2015-07-01 | 501bc380461e9c91c3ba3c3d9603f087 | 3 | | 21 | 2015-08-01 | 501bc380461e9c91c3ba3c3d9603f087 | 21 | | 16 | 2015-03-01 | acc96601808442120a3b840d1a6d8fb3 | 16 | | 17 | 2015-06-01 | acc96601808442120a3b840d1a6d8fb3 | 17 | | 18 | 2015-09-01 | acc96601808442120a3b840d1a6d8fb3 | 18 | | 13 | 2015-02-01 | da02ce650cd9f2496e9447bf2c51c20f | 13 | | 14 | 2015-05-01 | da02ce650cd9f2496e9447bf2c51c20f | 14 | | 15 | 2015-08-01 | da02ce650cd9f2496e9447bf2c51c20f | 15 | +----+------------+----------------------------------+----+ 27 rows in set (0.00 sec) mysql [localhost] {msandbox} (test) > explain partitions select * from t where c = '04d7e5edfa5c117fab259bcdb3a6bf8a' and d between '2015-04-01' and '2015-06-01' \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t partitions: Mar2015_Mar2015sp0,Jun2015_Jun2015sp0 type: ref possible_keys: c,d key: c key_len: 32 ref: const rows: 4 Extra: Using where; Using index 1 row in set (0.00 sec) mysql [localhost] {msandbox} (test) > explain partitions select * from t where c = '04d7e5edfa5c117fab259bcdb3a6bf8a' and d between '2015-04-31' and '2015-06-01' \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t partitions: Mar2015_Mar2015sp0,Jun2015_Jun2015sp0 type: ref possible_keys: c,d key: c key_len: 32 ref: const rows: 4 Extra: Using where; Using index 1 row in set (0.00 sec) mysql [localhost] {msandbox} (test) > explain partitions select * from t where c = '04d7e5edfa5c117fab259bcdb3a6bf8a' and d between '2015-05-01' and '2015-06-01' \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t partitions: Mar2015_Mar2015sp0,Jun2015_Jun2015sp0 type: ref possible_keys: c,d key: c key_len: 32 ref: const rows: 4 Extra: Using where; Using index 1 row in set (0.00 sec) mysql [localhost] {msandbox} (test) > explain partitions select * from t where c = '04d7e5edfa5c117fab259bcdb3a6bf8a' and d between '2015-06-01' and '2015-06-01' \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t partitions: Jun2015_Jun2015sp0 type: ref possible_keys: c,d key: d key_len: 3 ref: const rows: 1 Extra: Using where; Using index 1 row in set (0.00 sec)