How to use composite index in range query in mysql

Question

I have a table:

create table test (
 field1 bigint,
 field2 bigint
);
alter table test 
add index 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 doesn't 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 perfect answer.

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

• Philᵀᴹ: Edit your question with the proper create table statements, plus an explain of the query
• Rick James: A LIMIT without ORDER BY can lead to getting random rows.

Answer 1

You need a composite index on the columns (they are not fields!), but in reverse order, on (field2, field1) for this query.

When a condition has equality checks (=) and range/inequality checks (>, >=, <, <=, IN), then the columns involved in the equality checks should be first in the index and the inequality columns afterwards.

alter table test 
 add index ix_field2_field1 
 (field2, field1) ;

Additional notes:

• A query with LIMIT and without ORDER BY will yield indeterminate results. Always use ORDER BY with LIMIT unless you don't care if you get different results per execution:

  select * 
  from test 
   -- no need to force anything
  where field2 = 2 -- the order here doesn't matter
   and field1 > 100 -- but is useful for developers
  order by field2, field1
  limit 200 ;
  

• Really, choose some proper names for the columns, not "field1", "field2", etc.

• Your table does not have a PRIMARY KEY defined. This is not good both from a theoretical and a practical point of view. Assuming that the table uses the InnoDB engine, then by not defining a primary key, InnoDB adds a 6-byte hidden column that is used as the clustered index. This also means that all your indexes have this 6-byte column attached, making them wider. I suggest you have a - preferably narrow - PRIMARY KEY in all tables.

• Rick James: Cookbook that discusses tips like this.