snippetsqlModerate

How to use composite index in range query in mysql

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

rangequerymysqlcompositehowindexuse

Problem

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.

Solution

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 (>, >=,



- 
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.

Code Snippets

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

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 ;

Context

StackExchange Database Administrators Q#153156, answer score: 13

Revisions (0)

No revisions yet.