MySQL EXPLAIN not using index with WHERE + ORDER BY (both part of the index)?

Question

I've added an index (IDX_D34A04AD46C53D4C41FA5CD2) to my product table in order to speed-up searching for enabled products, ordered by price ascending:

CREATE TABLE `product` (
  `id` varchar(16) COLLATE utf8_unicode_ci NOT NULL,
  `unit_price` decimal(13,4) NOT NULL,
  `stock_qty` int(11) DEFAULT NULL,
  `is_enabled` tinyint(1) NOT NULL,
  `min_sale_qty` int(11) DEFAULT NULL,
  `max_sale_qty` int(11) DEFAULT NULL,
  `package_qty` int(11) DEFAULT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `is_new` tinyint(1) NOT NULL,
  `created_at` date NOT NULL,
  `package_type` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_D34A04ADBF396750` (`id`),
  KEY `IDX_D34A04AD41FA5CD2` (`unit_price`),
  KEY `IDX_D34A04AD46C53D4C` (`is_enabled`),
  KEY `IDX_D34A04AD46C53D4C41FA5CD2` (`is_enabled`,`unit_price`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Searching for active products, order by price, showing 50 items per page:

EXPLAIN SELECT * FROM product WHERE is_enabled > 0 ORDER BY unit_price ASC LIMIT 0, 50;

Output:

1   SIMPLE  product index   IDX_D34A04AD46C53D4C,IDX_D34A04AD46C53D4C41FA5CD2   IDX_D34A04AD41FA5CD2    6       100 Using where

Can you simple explain what I'm doing wrong and why I can't achieve "Using Index Condition" in my example?

EDIT: from MySQL documentation:

The following queries use the index to resolve the ORDER BY part:

SELECT * FROM t1 WHERE key_part1 = constant ORDER BY key_part2;

It seems exactly the same example as mine.

how many rows are in your table?

Eric Petroelje
– Eric Petroelje

2015-02-04 19:27:01 +00:00
Commented Feb 4, 2015 at 19:27 — Eric Petroelje
– Eric Petroelje, Commented Feb 4, 2015 at 19:27
@EricPetroelje right now ~9000

gremo
– gremo

2015-02-04 19:29:24 +00:00
Commented Feb 4, 2015 at 19:29 — gremo
– gremo, Commented Feb 4, 2015 at 19:29

Gordon Linoff · Accepted Answer · 2015-02-04 19:45:56Z

2

This is your query:

SELECT *
FROM product
WHERE is_enabled > 0
ORDER BY unit_price ASC
LIMIT 0, 50;

Because you have an inequality condition on is_enabled, it ends the index usage. So, the index cannot be used for unit_price. Or, alternatively, it could be used for unit_price and the sorting, but the filtering would be done on output.

The following should use the index:

SELECT *
FROM product
WHERE is_enabled = 1
ORDER BY unit_price ASC
LIMIT 0, 50;

answered Feb 4, 2015 at 19:45

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

gremo Over a year ago

Unfortunately the output of EXPLAIN is pretty the same.

gremo Over a year ago

Is there anything else I can do for understanding what's wrong? Products will be about ~20000 when I'll go in production and I'm afraid of performances.

Bill Karwin Over a year ago

@gremo, Gordon showed the solution in the second query: use = 1 comparison in your WHERE instead of > 0. If you search for one specific value in is_enabled then the matching rows can be sorted by unit_price. Create an index on those two columns, in that order.

Lucas · Accepted Answer · 2016-08-19 21:21:12Z

1

The value “Using index” in the "Extra" column indicates that MySQL will use a covering index to avoid accessing the table. This is not the case.

Your query is using the index (is_enabled,unit_price), but it's not using a covering index because you are retrieving all the columns in the SELECT statement.

answered Aug 19, 2016 at 21:21

Lucas

1115 bronze badges

Collectives™ on Stack Overflow

MySQL EXPLAIN not using index with WHERE + ORDER BY (both part of the index)?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related