phpmyadmin Question posted in
The official documentation can be found here.

MySQL Not Using the Best Index – Phpmyadmin

I have a simple select query:

SELECT *
FROM `inventory`
WHERE account_id = "1"
AND marketplace_id = "2"
AND sku = "3"
AND `date` = "4"

It has a condition in four fields, therefore, the natural index is of these four fields.

I have the following indexes:
enter image description here

and when I explain the query, I get:
enter image description here
Saying it is using the 2nd index from the list, rather than the 3rd or 4th.

If I use USE INDEX(account_id__marketplace_id__sku__date) in my query, it successfully uses it, and filters 100%.

I have tried ANALYZE TABLE and OPTIMIZE TABLE to rebuild the indexes, but the situation stays the same!

From my experience with other tables, if I now drop indexes 2 and 3, and recreate them, such that the largest index is the first in the order, the query will select that index, meaning, it selects the first that matches rather a maximal match.

  • MySQL Server version: 5.7.25-google-log
  • phpMyAdmin MySQL client version: 5.5.59
Tags:

2

Answers


  1. 0

    Your sample code has:

    date = "1"

    That is very arcane, because “1” is not a valid date. What I notice is no indexes use date. My guess is that you have a type conversion issue — and converting types (and function calls) can prevent the use of indexes.

    I would be sure that all the things being compared to are the right type.

    Login or Signup to reply.
  2. 0

    Mysql will choose index by the table’s data size;
    enter image description here

    You can try to insert 600000 records into inventory, keep record’s value random, and run “Explain”

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search