Can't able to reproduce with PS 5.5 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.00 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.01 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>