0

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.

1
  • What happens if you add an index on c3, c4, c1? – Lennart Dec 29 '20 at 22:29
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).

3
  • I understand that ORDER BY forced the usage of a particular index in this case. What I don’t understand is why so many rows need to be scanned. The first execution path was ‘range’ which means it’s already going to use an index. – buskila Dec 29 '20 at 0:41
  • I believe because it's using the index on c4 in the first query because of your predicate c4 < 195217615 which results a large range scan before the other predicates filter down the results. – J.D. Dec 29 '20 at 0:49
  • @buskila - EXPLAIN's "Rows" are estimated. In some cases they are reasonably accurate, in some cases, they are terribly far off. In particular, EXPLAIN usually ignores LIMIT which making an estimate. – Rick James Dec 31 '20 at 8:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.