mysql> show create table test \G
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
`city` varchar(10) DEFAULT NULL,
`dept_id` int(11) DEFAULT NULL,
`role` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `role_dept_id` (`role`,`dept_id`)
) ENGINE=InnoDB AUTO_INCREMENT=155 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> SELECT id, name, city FROM test WHERE dept_id = 3 AND role = 'manager' ORDER BY name, city;
+-----+-----------+-----------+
| id | name | city |
+-----+-----------+-----------+
| 141 | neel | pune |
| 125 | neel | pune |
| 122 | neel | pune |
| 133 | neel | pune |
| 87 | neel | pune |
| 129 | neel | pune |
| 83 | neel | pune |
| 137 | neel | pune |
| 79 | neel | pune |
| 75 | neel | pune |
| 140 | nilnandan | ahmedabad |
| 132 | nilnandan | ahmedabad |
| 136 | nilnandan | ahmedabad |
| 128 | nilnandan | ahmedabad |
| 124 | nilnandan | ahmedabad |
| 121 | nilnandan | ahmedabad |
| 86 | nilnandan | ahmedabad |
| 82 | nilnandan | ahmedabad |
| 78 | nilnandan | ahmedabad |
| 74 | nilnandan | ahmedabad |
+-----+-----------+-----------+
20 rows in set (0.01 sec)
mysql> EXPLAIN SELECT id, name, city FROM test WHERE dept_id = 3 AND role = 'manager' ORDER BY name, city;
+----+-------------+-------+------+---------------+--------------+---------+-------------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+--------------+---------+-------------+------+-----------------------------+
| 1 | SIMPLE | test | ref | role_dept_id | role_dept_id | 18 | const,const | 20 | Using where; Using filesort |
+----+-------------+-------+------+---------------+--------------+---------+-------------+------+-----------------------------+
1 row in set (0.00 sec)
mysql> SELECT id, name, city FROM test WHERE dept_id = 3 AND role = 'manager' ORDER BY name, city limit 0,8;
+-----+------+------+
| id | name | city |
+-----+------+------+
| 141 | neel | pune |
| 125 | neel | pune |
| 122 | neel | pune |
| 133 | neel | pune |
| 87 | neel | pune |
| 129 | neel | pune |
| 83 | neel | pune |
| 137 | neel | pune |
+-----+------+------+
8 rows in set (0.00 sec)
mysql> SELECT id, name, city FROM test WHERE dept_id = 3 AND role = 'manager' ORDER BY name, city limit 8,8;
+-----+-----------+-----------+
| id | name | city |
+-----+-----------+-----------+
| 79 | neel | pune |
| 75 | neel | pune |
| 140 | nilnandan | ahmedabad |
| 132 | nilnandan | ahmedabad |
| 136 | nilnandan | ahmedabad |
| 128 | nilnandan | ahmedabad |
| 124 | nilnandan | ahmedabad |
| 121 | nilnandan | ahmedabad |
+-----+-----------+-----------+
8 rows in set (0.00 sec)
mysql> SELECT id, name, city FROM test WHERE dept_id = 3 AND role = 'manager' ORDER BY name, city limit 16,8;
+----+-----------+-----------+
| id | name | city |
+----+-----------+-----------+
| 86 | nilnandan | ahmedabad |
| 82 | nilnandan | ahmedabad |
| 78 | nilnandan | ahmedabad |
| 74 | nilnandan | ahmedabad |
+----+-----------+-----------+
4 rows in set (0.00 sec)
Can't able to reproduce with PS 5.1.71
mysql> show create table test \G ******* ******* ****** 1. row ******* ******* ******* ******
*******
Table: test
Create Table: CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
`city` varchar(10) DEFAULT NULL,
`dept_id` int(11) DEFAULT NULL,
`role` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `role_dept_id` (`role`,`dept_id`)
) ENGINE=InnoDB AUTO_INCREMENT=155 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> SELECT id, name, city FROM test WHERE dept_id = 3 AND role = 'manager' ORDER BY name, city; ------- ----+-- ------- --+ ------- ----+-- ------- --+ ------- ----+-- ------- --+
+-----+
| id | name | city |
+-----+
| 141 | neel | pune |
| 125 | neel | pune |
| 122 | neel | pune |
| 133 | neel | pune |
| 87 | neel | pune |
| 129 | neel | pune |
| 83 | neel | pune |
| 137 | neel | pune |
| 79 | neel | pune |
| 75 | neel | pune |
| 140 | nilnandan | ahmedabad |
| 132 | nilnandan | ahmedabad |
| 136 | nilnandan | ahmedabad |
| 128 | nilnandan | ahmedabad |
| 124 | nilnandan | ahmedabad |
| 121 | nilnandan | ahmedabad |
| 86 | nilnandan | ahmedabad |
| 82 | nilnandan | ahmedabad |
| 78 | nilnandan | ahmedabad |
| 74 | nilnandan | ahmedabad |
+-----+
20 rows in set (0.01 sec)
mysql> EXPLAIN SELECT id, name, city FROM test WHERE dept_id = 3 AND role = 'manager' ORDER BY name, city; ------- -----+- ------+ ------+ ------- ------- -+----- ------- --+---- -----+- ------- -----+- -----+- ------- ------- ------- ------- + ------- -----+- ------+ ------+ ------- ------- -+----- ------- --+---- -----+- ------- -----+- -----+- ------- ------- ------- ------- + ------- -----+- ------+ ------+ ------- ------- -+----- ------- --+---- -----+- ------- -----+- -----+- ------- ------- ------- ------- +
+----+-
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-
| 1 | SIMPLE | test | ref | role_dept_id | role_dept_id | 18 | const,const | 20 | Using where; Using filesort |
+----+-
1 row in set (0.00 sec)
mysql> SELECT id, name, city FROM test WHERE dept_id = 3 AND role = 'manager' ORDER BY name, city limit 0,8; ------+ ------+ ------+ ------+ ------+ ------+
+-----+
| id | name | city |
+-----+
| 141 | neel | pune |
| 125 | neel | pune |
| 122 | neel | pune |
| 133 | neel | pune |
| 87 | neel | pune |
| 129 | neel | pune |
| 83 | neel | pune |
| 137 | neel | pune |
+-----+
8 rows in set (0.00 sec)
mysql> SELECT id, name, city FROM test WHERE dept_id = 3 AND role = 'manager' ORDER BY name, city limit 8,8; ------- ----+-- ------- --+ ------- ----+-- ------- --+ ------- ----+-- ------- --+
+-----+
| id | name | city |
+-----+
| 79 | neel | pune |
| 75 | neel | pune |
| 140 | nilnandan | ahmedabad |
| 132 | nilnandan | ahmedabad |
| 136 | nilnandan | ahmedabad |
| 128 | nilnandan | ahmedabad |
| 124 | nilnandan | ahmedabad |
| 121 | nilnandan | ahmedabad |
+-----+
8 rows in set (0.00 sec)
mysql> SELECT id, name, city FROM test WHERE dept_id = 3 AND role = 'manager' ORDER BY name, city limit 16,8; ------- ---+--- ------- -+ ------- ---+--- ------- -+ ------- ---+--- ------- -+
+----+-
| id | name | city |
+----+-
| 86 | nilnandan | ahmedabad |
| 82 | nilnandan | ahmedabad |
| 78 | nilnandan | ahmedabad |
| 74 | nilnandan | ahmedabad |
+----+-
4 rows in set (0.00 sec)
mysql>