I still didn't able to reproduce it. I tried to use the same percentage of data distribution among different table fields but no luck yet. It uses the optimal index i.e. PRIMARY KEY for queries regardless used with LIMIT clause or not. It is possible for you to share test data to try to reproduce ?
mysql [localhost] {root} (test) > SHOW GLOBAL VARIABLES LIKE '%version%';
+-------------------------+--------------------------------------------------+
| Variable_name | Value |
+-------------------------+--------------------------------------------------+
| innodb_version | 5.6.19-rel67.0 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.6.19-67.0 |
| version_comment | Percona Server (GPL), Release 67.0, Revision 618 |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+--------------------------------------------------+
7 rows in set (0.00 sec)
mysql [localhost] {root} (test) > show create table suggestion\G
*************************** 1. row ***************************
Table: suggestion
Create Table: CREATE TABLE `suggestion` (
`user_id` int(10) unsigned NOT NULL,
`type` enum('user','group','board','newbie','dating') COLLATE latin1_german2_ci NOT NULL,
`obj_id` int(10) unsigned NOT NULL,
`rejected` enum('y','n') COLLATE latin1_german2_ci NOT NULL DEFAULT 'n',
`flags` int(10) unsigned NOT NULL DEFAULT '0',
`data` varbinary(32768) NOT NULL DEFAULT '',
`created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`user_id`,`type`,`obj_id`),
KEY `user_id` (`user_id`,`rejected`),
KEY `type` (`type`,`created`),
KEY `rejected` (`rejected`,`type`,`created`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci
-rw-rw---- 1 root root 41K Oct 31 09:08 suggestion.frm
-rw-rw---- 1 root root 232M Oct 31 09:13 suggestion.ibd
mysql [localhost] {root} (test) > SELECT type, COUNT(type) FROM suggestion GROUP BY type;
+--------+-------------+
| type | COUNT(type) |
+--------+-------------+
| user | 183817 |
| group | 184566 |
| board | 184760 |
| newbie | 183507 |
| dating | 1107810 |
+--------+-------------+
5 rows in set (0.55 sec)
mysql [localhost] {root} (test) > SELECT rejected, COUNT(rejected) FROM suggestion GROUP BY rejected;
+----------+-----------------+
| rejected | COUNT(rejected) |
+----------+-----------------+
| y | 183675 |
| n | 1660785 |
+----------+-----------------+
2 rows in set (0.59 sec)
mysql [localhost] {root} (test) > SELECT flags, COUNT(flags) FROM suggestion GROUP BY flags;
+-------+--------------+
| flags | COUNT(flags) |
+-------+--------------+
| 0 | 1244460 |
| 1 | 500000 |
| 2 | 55539 |
| 3 | 44461 |
+-------+--------------+
4 rows in set (3.11 sec)
mysql [localhost] {root} (test) > select count(*) from suggestion where user_id=7723036;
+----------+
| count(*) |
+----------+
| 150 |
+----------+
1 row in set (0.05 sec)
mysql [localhost] {root} (test) > EXPLAIN SELECT obj_id, flags, data, type FROM suggestion WHERE user_id='7723036' AND type IN ('newbie', 'group') AND rejected='n' AND flags=0 ORDER BY type DESC, created DESC LIMIT 50;
+----+-------------+------------+-------+-------------------------------+---------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+-------------------------------+---------+---------+------+------+-----------------------------+
| 1 | SIMPLE | suggestion | range | PRIMARY,user_id,type,rejected | PRIMARY | 5 | NULL | 35 | Using where; Using filesort |
+----+-------------+------------+-------+-------------------------------+---------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)
mysql [localhost] {root} (test) > EXPLAIN SELECT obj_id, flags, data, type FROM suggestion WHERE user_id='7723036' AND type IN ('newbie', 'group') AND rejected='n' AND flags=0 ORDER BY type DESC, created DESC;
+----+-------------+------------+-------+-------------------------------+---------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+-------------------------------+---------+---------+------+------+-----------------------------+
| 1 | SIMPLE | suggestion | range | PRIMARY,user_id,type,rejected | PRIMARY | 5 | NULL | 35 | Using where; Using filesort |
+----+-------------+------------+-------+-------------------------------+---------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)
mysql [localhost] {root} (test) > EXPLAIN SELECT obj_id, flags, data, type FROM suggestion FORCE INDEX(type) WHERE user_id='7723036' AND type IN ('newbie', 'group') AND rejected='n' AND flags=0 ORDER BY type DESC, created DESC LIMIT 50;
+----+-------------+------------+-------+---------------+------+---------+------+--------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+---------------+------+---------+------+--------+------------------------------------+
| 1 | SIMPLE | suggestion | range | type | type | 1 | NULL | 706824 | Using index condition; Using where |
+----+-------------+------------+-------+---------------+------+---------+------+--------+------------------------------------+
1 row in set (0.00 sec)
I still didn't able to reproduce it. I tried to use the same percentage of data distribution among different table fields but no luck yet. It uses the optimal index i.e. PRIMARY KEY for queries regardless used with LIMIT clause or not. It is possible for you to share test data to try to reproduce ?
mysql [localhost] {root} (test) > SHOW GLOBAL VARIABLES LIKE '%version%'; ------- ------- -----+- ------- ------- ------- ------- ------- ------- ------- + ------- ------- -----+- ------- ------- ------- ------- ------- ------- ------- + conversions | | compile_ machine | x86_64 | ------- ------- -----+- ------- ------- ------- ------- ------- ------- ------- +
+------
| Variable_name | Value |
+------
| innodb_version | 5.6.19-rel67.0 |
| protocol_version | 10 |
| slave_type_
| version | 5.6.19-67.0 |
| version_comment | Percona Server (GPL), Release 67.0, Revision 618 |
| version_
| version_compile_os | Linux |
+------
7 rows in set (0.00 sec)
mysql [localhost] {root} (test) > show create table suggestion\G ******* ******* ****** 1. row ******* ******* ******* ****** ,'group' ,'board' ,'newbie' ,'dating' ) COLLATE latin1_german2_ci NOT NULL, id`,`type` ,`obj_id` ), id`,`rejected` ), ,`type` ,`created` ) latin1_ german2_ ci
*******
Table: suggestion
Create Table: CREATE TABLE `suggestion` (
`user_id` int(10) unsigned NOT NULL,
`type` enum('user'
`obj_id` int(10) unsigned NOT NULL,
`rejected` enum('y','n') COLLATE latin1_german2_ci NOT NULL DEFAULT 'n',
`flags` int(10) unsigned NOT NULL DEFAULT '0',
`data` varbinary(32768) NOT NULL DEFAULT '',
`created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`user_
KEY `user_id` (`user_
KEY `type` (`type`,`created`),
KEY `rejected` (`rejected`
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=
mysql [localhost] {root} (test) > show table status like 'suggestion'\G ******* ******* ****** 1. row ******* ******* ******* ******
*******
Name: suggestion
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 1836214
Avg_row_length: 75
Data_length: 139132928
Max_data_length: 0
Index_length: 102465536
Data_free: 0
Auto_increment: NULL
Create_time: 2014-10-31 09:10:04
Update_time: NULL
Check_time: NULL
Collation: latin1_german2_ci
Checksum: NULL
Create_options:
Comment:
-rw-rw---- 1 root root 41K Oct 31 09:08 suggestion.frm
-rw-rw---- 1 root root 232M Oct 31 09:13 suggestion.ibd
mysql [localhost] {root} (test) > SELECT type, COUNT(type) FROM suggestion GROUP BY type; --+---- ------- --+ --+---- ------- --+ --+---- ------- --+
+------
| type | COUNT(type) |
+------
| user | 183817 |
| group | 184566 |
| board | 184760 |
| newbie | 183507 |
| dating | 1107810 |
+------
5 rows in set (0.55 sec)
mysql [localhost] {root} (test) > SELECT rejected, COUNT(rejected) FROM suggestion GROUP BY rejected; ----+-- ------- ------- -+ ----+-- ------- ------- -+ ----+-- ------- ------- -+
+------
| rejected | COUNT(rejected) |
+------
| y | 183675 |
| n | 1660785 |
+------
2 rows in set (0.59 sec)
mysql [localhost] {root} (test) > SELECT flags, COUNT(flags) FROM suggestion GROUP BY flags; -+----- ------- --+ -+----- ------- --+ -+----- ------- --+
+------
| flags | COUNT(flags) |
+------
| 0 | 1244460 |
| 1 | 500000 |
| 2 | 55539 |
| 3 | 44461 |
+------
4 rows in set (3.11 sec)
mysql [localhost] {root} (test) > select count(*) from suggestion where user_id=7723036;
+----------+
| count(*) |
+----------+
| 150 |
+----------+
1 row in set (0.05 sec)
mysql [localhost] {root} (test) > EXPLAIN SELECT obj_id, flags, data, type FROM suggestion WHERE user_id='7723036' AND type IN ('newbie', 'group') AND rejected='n' AND flags=0 ORDER BY type DESC, created DESC LIMIT 50; ------- -----+- ------- ----+-- -----+- ------- ------- ------- ------- --+---- -----+- ------- -+----- -+----- -+----- ------- ------- ------- ---+ ------- -----+- ------- ----+-- -----+- ------- ------- ------- ------- --+---- -----+- ------- -+----- -+----- -+----- ------- ------- ------- ---+ user_id, type,rejected | PRIMARY | 5 | NULL | 35 | Using where; Using filesort | ------- -----+- ------- ----+-- -----+- ------- ------- ------- ------- --+---- -----+- ------- -+----- -+----- -+----- ------- ------- ------- ---+
+----+-
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-
| 1 | SIMPLE | suggestion | range | PRIMARY,
+----+-
1 row in set (0.00 sec)
mysql [localhost] {root} (test) > EXPLAIN SELECT obj_id, flags, data, type FROM suggestion WHERE user_id='7723036' AND type IN ('newbie', 'group') AND rejected='n' AND flags=0 ORDER BY type DESC, created DESC; ------- -----+- ------- ----+-- -----+- ------- ------- ------- ------- --+---- -----+- ------- -+----- -+----- -+----- ------- ------- ------- ---+ ------- -----+- ------- ----+-- -----+- ------- ------- ------- ------- --+---- -----+- ------- -+----- -+----- -+----- ------- ------- ------- ---+ user_id, type,rejected | PRIMARY | 5 | NULL | 35 | Using where; Using filesort | ------- -----+- ------- ----+-- -----+- ------- ------- ------- ------- --+---- -----+- ------- -+----- -+----- -+----- ------- ------- ------- ---+
+----+-
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-
| 1 | SIMPLE | suggestion | range | PRIMARY,
+----+-
1 row in set (0.00 sec)
mysql [localhost] {root} (test) > EXPLAIN SELECT obj_id, flags, data, type FROM suggestion FORCE INDEX(type) WHERE user_id='7723036' AND type IN ('newbie', 'group') AND rejected='n' AND flags=0 ORDER BY type DESC, created DESC LIMIT 50; ------- -----+- ------- ----+-- -----+- ------- ------- +------ +------ ---+--- ---+--- -----+- ------- ------- ------- ------- ------- + ------- -----+- ------- ----+-- -----+- ------- ------- +------ +------ ---+--- ---+--- -----+- ------- ------- ------- ------- ------- + ------- -----+- ------- ----+-- -----+- ------- ------- +------ +------ ---+--- ---+--- -----+- ------- ------- ------- ------- ------- +
+----+-
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-
| 1 | SIMPLE | suggestion | range | type | type | 1 | NULL | 706824 | Using index condition; Using where |
+----+-
1 row in set (0.00 sec)