Mysql multiple column index using wrong index

Question

I have indexes on products table:

PRIMARY
products_gender_id_foreign
products_subcategory_id_foreign
idx_products_cat_subcat_gender_used (multiple column index)

QUERY:

select `id`, `name`, `price`, `images`, `used`
from `products`
where `category_id` = '1' and
      `subcategory_id` = '2' and
      `gender_id` = '1' and
      `used` = '0'
order by `created_at` desc
limit 24 offset 0

Question:

Why mysql uses index

products_subcategory_id_foreign

insted of

idx_products_cat_subcat_gender_used (multiple column index)

HERE IS EXPLAIN :

1 SIMPLE products NULL ref products_gender_id_foreign,products_subcategory_id... products_subcategory_id_foreign 5 const 2 2.50 Using index condition; Using where; Using filesort

You seem to have a very small number of rows, so the specific index doesn't really matter. — Gordon Linoff
– Gordon Linoff, Commented Aug 17, 2018 at 22:27
Any query-optimization question should include the output of SHOW CREATE TABLE <name> — Bill Karwin
– Bill Karwin, Commented Aug 17, 2018 at 22:33
while hard to read your index names, created_at potentially could be added to the end of the index. Try not to quote number if the fields are numberic. — danblack
– danblack, Commented Aug 18, 2018 at 6:26
You need to add an index to created_at to possibly avoid filesort. — LMC
– LMC, Commented Aug 23, 2018 at 13:44
@LuisMuñoz - Adding created_at will not avoid filesort unless the index also handles all of the WHERE. — Rick James
– Rick James, Commented Aug 27, 2018 at 4:56

Arturo Seijas · Accepted Answer · 2018-10-21 11:24:56Z

2

As explained in the MySQL documentation, a index can be ignored in some circunstances. The ones that could apply in your case, as one index is already beeing used, are:

You are comparing indexed columns with constant values and MySQL has calculated (based on the index tree) that the constants cover too large a part of the table and that a table scan would be faster. See Section 8.2.1.1, “WHERE Clause Optimization”.
You are using a key with low cardinality (many rows match the key value) through another column. In this case, MySQL assumes that by using the key it probably will do many key lookups and that a table scan would be faster.

My guess is that the values of category_id are not sparse enough

edited Oct 21, 2018 at 11:24

answered Aug 23, 2018 at 15:01

Arturo Seijas

3022 silver badges8 bronze badges

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

Comments

Rick James · Accepted Answer · 2018-08-27 04:50:56Z

1

As I say here, this

where `category_id` = '1' and
      `subcategory_id` = '2' and
      `gender_id` = '1' and
      `used` = '0'
order by `created_at` desc
limit 24 offset 0

needs a 5-column composite index:

INDEX(category_id, subcategory_id, gender_id, used,  -- in any order
      created_at)

to get to the LIMIT, thereby not having to fetch lots of rows and sort them.

As for your actual question about which index it picked... Probably the cardinality of one inadequate index was better than the other.

answered Aug 27, 2018 at 4:50

Rick James

144k15 gold badges144 silver badges255 bronze badges

Collectives™ on Stack Overflow

Mysql multiple column index using wrong index

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related