1

I have a table "test" with two field, field1 and field2, and an composite index created field1_field2(field1, field2). here is a sql : select * from test force index(field1_field2) where field1 > 100 and field2 = 2 limit 200

and it doesnt run very well. I Explained it and found that only field1 condition used by index query, field2 condition was ignored....

I want know why, and I need a perfectly answer

there are lots of data with field1 > 100 and lots of data with field2 = 2

Mr rain
  • 983
  • 5
  • 13
  • 27

1 Answers1

4

For that query, you need INDEX(field2, field1), in that order. Put the = columns first in an index. Once you hit a "range" (such as >), that's the last part of the index that will be used.

Here's an Index Cookbook .

Rick James
  • 135,179
  • 13
  • 127
  • 222