2

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
Amit
  • 5,924
  • 7
  • 46
  • 94
  • https://www.mysqltutorial.org/mysql-index/mysql-force-index/ – RiggsFolly Mar 26 '20 at 10:08
  • https://stackoverflow.com/questions/19061873/force-index-mysql-where-do-i-put-it – RiggsFolly Mar 26 '20 at 10:10
  • 1
    @RiggsFolly As I said, "USE INDEX" does work. I'm trying to get this to work WITHOUT requiring USE INDEX – Amit Mar 26 '20 at 10:12
  • 1
    Show table's DDL. Give statistic - total records count, selected records count, distinct values count for each of 4 fields. Does this table is often readed and rare updated? And - do you really need ALL fields in output? – Akina Mar 26 '20 at 10:17
  • 1
    @Akina The cardinality stats give you the distinct count per field. I'm not asking to change the query such that it would fit the index, I'm asking, as it is now, why doesn't it use the index, and when I do change the index order it uses the correct index – Amit Mar 26 '20 at 10:45
  • @Amit If you don't understand the meaning of the question, it doesn't mean that the question doesn't make sense. – Akina Mar 26 '20 at 10:48
  • Additionally - test ```WHERE (account_id, marketplace_id, sku, `date`) = ROW(1, 2, 3, 4)``` - does it uses this index? – Akina Mar 26 '20 at 10:53
  • @Akina It uses the first index in the list (so same index as the issue) if I use `ROW("1","2","3","4")` (because they are varchars) – Amit Mar 26 '20 at 11:01
  • Don't use `FORCE INDEX`. Even if it runs faster today, it may be worse tomorrow. – Rick James Mar 27 '20 at 22:39

2 Answers2

1

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"

Nikos Hidalgo
  • 3,666
  • 9
  • 25
  • 39
Hopes Shi
  • 26
  • 2
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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thank you. Good try. I created an index for only account id, marketplace id, and sku and made a query only for these fields. issue persists. – Amit Mar 26 '20 at 12:56
  • 1
    @Amit . . . You want a compound index. My point is that type conversion could prevent the use of an index that you want used. Be sure all the comparands are of the same type. – Gordon Linoff Mar 26 '20 at 14:16