WHERE...LIMIT much slower than WHERE...ORDER BY...LIMIT in MySQL 5.5

Question

I'm using MySQL 5.5 (but i see the same behavior on 5.7)

Here is the table schema:

CREATE TABLE `so_table` (
  `p` char(16) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `c1` int(10) DEFAULT NULL,
  `c2` smallint(5) NOT NULL,
  `c3` tinyint(3) NOT NULL,
  `c4` int(10) NOT NULL,
  PRIMARY KEY (`p`),
  INDEX `so_table_index_1` (`c1`,`c2`),
  INDEX `so_table_index_2` (`c4`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8

The table contains around 2 million rows. The query below is considered a slow query and sometimes takes 20 seconds to execute:

SELECT p, c1 FROM so_table WHERE c1 IS NOT NULL AND c3 = 1 AND c4 < 195217615 LIMIT 100;

Running EXPLAIN on the query above returns the following:

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	so_table	NULL	range	so_table_index_1,so_table_index_2	so_table_index_1	5	NULL	1,281,736	Using index condition; Using where

I was reading about optimizing LIMIT and it seems that adding ORDER BY makes things better, so the following query indeed has a better execution plan (i haven't tried it on the real data though)

SELECT p, c1 FROM so_table WHERE c1 IS NOT NULL AND c3 = 1 AND c4 < 203322189 ORDER BY p ASC LIMIT 100;

And EXPLAIN shows the following:

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	so_table	NULL	index	so_table_index_1,so_table_index_2	PRIMARY	48	NULL	428	Using index condition; Using where

So it seems that this query is going to be faster. My guess is that ORDER BY forces the optimizer to use the primary index which is unique and maybe this helps the optimizer decide to not actually run through all rows. I was reading MySQL docs, but i couldn't figure out if my theory is right or what's the real reason is.

Answer 1

By adding the ORDER BY clause to your query, it resulted in the query optimizer choosing a different execution plan that uses the index to seek out the rows (which was a lot less rows to seek) as opposed to scanning the range of a lot more rows in your first query.

This isn't a guarantee that ORDER BY will always cause this to happen, but in this case it influenced the query optimizer to choose a different operation that happened to be more efficient (likely because the index it used is already sorted on the field you're ordering by).