How to use composite index in range query in mysql

Question

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

Try swapping your index columns to (field2, field1).

JimmyB
– JimmyB

2016-10-24 08:43:27 +00:00
Commented Oct 24, 2016 at 8:43 — JimmyB
– JimmyB, Commented Oct 24, 2016 at 8:43
Also, read up here.

JimmyB
– JimmyB

2016-10-24 09:17:29 +00:00
Commented Oct 24, 2016 at 9:17 — JimmyB
– JimmyB, Commented Oct 24, 2016 at 9:17

Rick James · Accepted Answer · 2016-12-24 17:45:25Z

6

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 .

edited Dec 24, 2016 at 17:45

answered Oct 26, 2016 at 5:54

Rick James

144k15 gold badges144 silver badges255 bronze badges

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

Collectives™ on Stack Overflow

How to use composite index in range query in mysql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related