Description:
I have a test case where the database should use the PRIMARY KEY to find a
row in a partitioned table but entire partitions are scanned instead.
How to repeat:
mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`week_of_year` tinyint(2) unsigned NOT NULL,
`type` tinyint(2) unsigned NOT NULL,
`external_userid` bigint(20) unsigned NOT NULL,
`status_id` varchar(20) NOT NULL,
`text` varchar(255) NOT NULL DEFAULT '',
`link` varchar(255) NOT NULL,
PRIMARY KEY (`status_id`,`external_userid`,`type`,`week_of_year`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY LIST (MOD(week_of_year,10))
(PARTITION week0 VALUES IN (0) ENGINE = InnoDB,
PARTITION week1 VALUES IN (1) ENGINE = InnoDB,
PARTITION week2 VALUES IN (2) ENGINE = InnoDB,
PARTITION week3 VALUES IN (3) ENGINE = InnoDB,
PARTITION week4 VALUES IN (4) ENGINE = InnoDB,
PARTITION week5 VALUES IN (5) ENGINE = InnoDB,
PARTITION week6 VALUES IN (6) ENGINE = InnoDB,
PARTITION week7 VALUES IN (7) ENGINE = InnoDB,
PARTITION week8 VALUES IN (8) ENGINE = InnoDB,
PARTITION week9 VALUES IN (9) ENGINE = InnoDB) */
1 row in set (0.01 sec)
mysql> show create table t2\G
ERROR 1146 (42S02): Table 'test.t2' doesn't exist
mysql> show create table t1_hash\G
*************************** 1. row ***************************
Table: t1_hash
Create Table: CREATE TABLE `t1_hash` (
`week_of_year` tinyint(2) unsigned NOT NULL,
`type` tinyint(2) unsigned NOT NULL,
`external_userid` bigint(20) unsigned NOT NULL,
`status_id` varchar(20) NOT NULL,
`text` varchar(255) NOT NULL DEFAULT '',
`link` varchar(255) NOT NULL,
PRIMARY KEY (`status_id`,`external_userid`,`type`,`week_of_year`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (week_of_year)
PARTITIONS 10 */
1 row in set (0.00 sec)
As you can see, neither of these tables use the PRIMARY KEY when
all of the columns for the PRIMARY KEY have been provided.
mysql> explain partitions select * from t1 where status_id = 10152429551487952 and type =2 and external_userid = 8062627951 and week_of_year = 22\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: week2
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 26863
Extra: Using where
1 row in set (0.00 sec)
mysql> explain partitions select * from t1_hash where status_id = 10152429551487952 and type =2 and external_userid = 8062627951 and week_of_year = 22\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1_hash
partitions: p2
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 26513
Extra: Using where
1 row in set (0.00 sec)
Description: I have a test case where the database should use the PRIMARY KEY to find a row in a partitioned table but entire partitions are scanned instead. How to repeat: mysql> show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `week_of_year` tinyint(2) unsigned NOT NULL, `type` tinyint(2) unsigned NOT NULL, `external_userid` bigint(20) unsigned NOT NULL, `status_id` varchar(20) NOT NULL, `text` varchar(255) NOT NULL DEFAULT '', `link` varchar(255) NOT NULL, PRIMARY KEY (`status_id`,`external_userid`,`type`,`week_of_year`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (MOD(week_of_year,10)) (PARTITION week0 VALUES IN (0) ENGINE = InnoDB, PARTITION week1 VALUES IN (1) ENGINE = InnoDB, PARTITION week2 VALUES IN (2) ENGINE = InnoDB, PARTITION week3 VALUES IN (3) ENGINE = InnoDB, PARTITION week4 VALUES IN (4) ENGINE = InnoDB, PARTITION week5 VALUES IN (5) ENGINE = InnoDB, PARTITION week6 VALUES IN (6) ENGINE = InnoDB, PARTITION week7 VALUES IN (7) ENGINE = InnoDB, PARTITION week8 VALUES IN (8) ENGINE = InnoDB, PARTITION week9 VALUES IN (9) ENGINE = InnoDB) */ 1 row in set (0.01 sec) mysql> show create table t2\G ERROR 1146 (42S02): Table 'test.t2' doesn't exist mysql> show create table t1_hash\G *************************** 1. row *************************** Table: t1_hash Create Table: CREATE TABLE `t1_hash` ( `week_of_year` tinyint(2) unsigned NOT NULL, `type` tinyint(2) unsigned NOT NULL, `external_userid` bigint(20) unsigned NOT NULL, `status_id` varchar(20) NOT NULL, `text` varchar(255) NOT NULL DEFAULT '', `link` varchar(255) NOT NULL, PRIMARY KEY (`status_id`,`external_userid`,`type`,`week_of_year`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (week_of_year) PARTITIONS 10 */ 1 row in set (0.00 sec) As you can see, neither of these tables use the PRIMARY KEY when all of the columns for the PRIMARY KEY have been provided. mysql> explain partitions select * from t1 where status_id = 10152429551487952 and type =2 and external_userid = 8062627951 and week_of_year = 22\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 partitions: week2 type: ALL possible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 26863 Extra: Using where 1 row in set (0.00 sec) mysql> explain partitions select * from t1_hash where status_id = 10152429551487952 and type =2 and external_userid = 8062627951 and week_of_year = 22\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1_hash partitions: p2 type: ALL possible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 26513 Extra: Using where 1 row in set (0.00 sec)