CODEWITHSUNDEEP
mysqlsqloptimizationindexingquery-optimization
Nikola Vi
Nikola Vi

Reputation: 120

Mysql multiple column index using wrong index

I have indexes on products table:

  1. PRIMARY
  2. products_gender_id_foreign
  3. products_subcategory_id_foreign
  4. 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

Upvotes: 0

Views: 426

Answers (2)

Arturo Seijas
Arturo Seijas

Reputation: 304

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

Upvotes: 2

Rick James
Rick James

Reputation: 142208

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.

Upvotes: 1

Related Questions