| Bug #70001 | Partitioned tables do not use ICP - severe performance loss after partitioning | ||
|---|---|---|---|
| Submitted: | 12 Aug 2013 18:46 | Modified: | 6 Nov 2013 15:18 |
| Reporter: | Justin Swanhart | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Partitions | Severity: | S1 (Critical) |
| Version: | 5.6.13 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[12 Aug 2013 18:51]
Justin Swanhart
see support/incoming/70001.sql
[13 Aug 2013 17:28]
MySQL Verification Team
Hello Justin, Thank you for the bug report and the test case. Verified as described. Thanks, Umesh
[6 Nov 2013 15:18]
Jon Stephens
Fixed in 5.7. Documented in the 5.7.3 changelog as follows:
Index condition pushdown did not work with partitioned tables.
Closed.
[13 Apr 2017 11:11]
Rimantas Ragainis
This issue is till present on 5.7.17 Any estimations on when it will be fixed?
[23 Jun 2017 8:20]
Valeriy Kravchuk
Rimantas,
Can you, please, upload a test case that shows the problem with 5.7.17? In my primitive test everything works as expected:
mysql> show create table cr\G
*************************** 1. row ***************************
Table: cr
Create Table: CREATE TABLE `cr` (
`id` int(11) NOT NULL,
`r1` int(11) DEFAULT NULL,
`r2` int(11) DEFAULT NULL,
`dt1` datetime DEFAULT NULL,
`dt2` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> show create table cc\G
*************************** 1. row ***************************
Table: cc
Create Table: CREATE TABLE `cc` (
`id` int(11) NOT NULL,
`cr_id` int(11) DEFAULT NULL,
`rt` datetime DEFAULT NULL,
`other` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `k2` (`cr_id`,`rt`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> select count(*) from cc;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from cr;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
mysql> explain select cc.*, max(r2 - r1) from cr join cc on (cr.id = cc.cr_id and rt between dt1 and dt2);
+----+-------------+-------+------------+------+---------------+------+---------+------------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------------+------+----------+-----------------------+
| 1 | SIMPLE | cr | NULL | ALL | PRIMARY | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 1 | SIMPLE | cc | NULL | ref | k2 | k2 | 5 | test.cr.id | 1 | 100.00 | Using index condition |
+----+-------------+-------+------------+------+---------------+------+---------+------------+------+----------+-----------------------+
2 rows in set, 1 warning (0.00 sec)
So, we have ICP used. Now, let me partition the cc table in a simple way:
mysql> alter table cc partition by hash(id) partitions 4;
Query OK, 0 rows affected (1.58 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select cc.*, max(r2 - r1) from cr join cc on (cr.id = cc.cr_id and rt between dt1 and dt2);
+----+-------------+-------+-------------+------+---------------+------+---------+------------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+-------------+------+---------------+------+---------+------------+------+----------+-----------------------+
| 1 | SIMPLE | cr | NULL | ALL | PRIMARY | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 1 | SIMPLE | cc | p0,p1,p2,p3 | ref | k2 | k2 | 5 | test.cr.id | 1 | 100.00 | Using index condition |
+----+-------------+-------+-------------+------+---------------+------+---------+------------+------+----------+-----------------------+
2 rows in set, 1 warning (0.27 sec)
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.17 |
+-----------+
1 row in set (0.00 sec)
As you can see, ICP is again used.
[23 Jun 2017 9:11]
Rimantas Ragainis
Sure. I used the same table structure for two tables - one is partitioned by date ("test_partitioned")
and the other one isn't ("test_solid"). See SQL queries bellow:
CREATE TABLE `test_partitioned` (
`service_id` int(10) unsigned NOT NULL,
`uid` varchar(32) NOT NULL DEFAULT '',
`date` date NOT NULL DEFAULT '0000-00-00',
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`ord_rev` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`service_id`,`uid`,`ord_rev`,`date`)
) ENGINE=InnoDB DEFAULT CHARSET=ascii ROW_FORMAT=COMPRESSED
/*!50100 PARTITION BY RANGE (TO_DAYS(`date`))
(PARTITION p20161001 VALUES LESS THAN (736634) ENGINE = InnoDB,
PARTITION p20161101 VALUES LESS THAN (736664) ENGINE = InnoDB,
PARTITION p20161201 VALUES LESS THAN (736695) ENGINE = InnoDB,
PARTITION p20170101 VALUES LESS THAN (736726) ENGINE = InnoDB,
PARTITION p20170201 VALUES LESS THAN (736754) ENGINE = InnoDB,
PARTITION p20170301 VALUES LESS THAN (736785) ENGINE = InnoDB,
PARTITION p20170401 VALUES LESS THAN (736815) ENGINE = InnoDB,
PARTITION p20170501 VALUES LESS THAN (736846) ENGINE = InnoDB) */;
INSERT INTO `test_partitioned` (`service_id`, `uid`, `date`, `created`, `ord_rev`)
VALUES
(1,'user123','2016-12-12','2016-12-12 07:38:07',9223223884026074696),
(1,'user123','2017-01-04','2017-01-04 13:55:37',9223223683041068956),
(1,'user123','2017-02-14','2017-02-14 11:17:38',9223223329748968486);
CREATE TABLE `test_solid` (
`service_id` int(10) unsigned NOT NULL,
`uid` varchar(32) NOT NULL DEFAULT '',
`date` date NOT NULL DEFAULT '0000-00-00',
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`ord_rev` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`service_id`,`uid`,`ord_rev`,`date`)
) ENGINE=InnoDB DEFAULT CHARSET=ascii ROW_FORMAT=COMPRESSED;
INSERT INTO `test_solid` (`service_id`, `uid`, `date`, `created`, `ord_rev`)
VALUES
(1,'user123','2017-02-14','2017-02-14 11:17:38',9223223329748968486),
(1,'user123','2017-01-04','2017-01-04 13:55:37',9223223683041068956),
(1,'user123','2016-12-12','2016-12-12 07:38:07',9223223884026074696);
mysql> select * from test_partitioned where service_id=1;
+------------+---------+------------+---------------------+---------------------+
| service_id | uid | date | created | ord_rev |
+------------+---------+------------+---------------------+---------------------+
| 1 | user123 | 2016-12-12 | 2016-12-12 07:38:07 | 9223223884026074696 |
| 1 | user123 | 2017-01-04 | 2017-01-04 13:55:37 | 9223223683041068956 |
| 1 | user123 | 2017-02-14 | 2017-02-14 11:17:38 | 9223223329748968486 |
+------------+---------+------------+---------------------+---------------------+
3 rows in set (0.00 sec)
mysql> select * from test_solid where service_id=1;
+------------+---------+------------+---------------------+---------------------+
| service_id | uid | date | created | ord_rev |
+------------+---------+------------+---------------------+---------------------+
| 1 | user123 | 2017-02-14 | 2017-02-14 11:17:38 | 9223223329748968486 |
| 1 | user123 | 2017-01-04 | 2017-01-04 13:55:37 | 9223223683041068956 |
| 1 | user123 | 2016-12-12 | 2016-12-12 07:38:07 | 9223223884026074696 |
+------------+---------+------------+---------------------+---------------------+
3 rows in set (0.01 sec)
As you can see from my example, the data from "test_solid" only is returned ordered ascending by primary key as expected.
Description: mysql> explain SELECT cust_id AS expr$0,MAX(odometer_reading - issued_odometer) AS expr_3466088376 FROM cr AS `car_read` JOIN cc AS `cust_car` ON( car_read.car_id = cust_car.car_id AND read_datetime between assigned_datetime AND unassigned_datetime ) \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: cust_car type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 25944 Extra: NULL *************************** 2. row *************************** id: 1 select_type: SIMPLE table: car_read type: ref possible_keys: idx_car_read_datetime key: idx_car_read_datetime key_len: 4 ref: test.cust_car.car_id rows: 168 Extra: Using index condition 2 rows in set (0.00 sec) mysql> explain SELECT cust_id AS expr$0,MAX(odometer_reading - issued_odometer) AS expr_3466088376 FROM car_read AS `car_read` JOIN cust_car AS `cust_car` ON( car_read.car_id = cust_car.car_id AND read_datetime between assigned_datetime AND unassigned_datetime ) \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: cust_car type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 25554 Extra: NULL *************************** 2. row *************************** id: 1 select_type: SIMPLE table: car_read type: ref possible_keys: idx_car_read_datetime key: idx_car_read_datetime key_len: 4 ref: test.cust_car.car_id rows: 13 Extra: Using where 2 rows in set (0.00 sec) How to repeat: see above. data will be attached. Suggested fix: Use the same query plans for partitioned tables as regular tables.