4

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.

2
  • 1
    Edit your question with the proper create table statements, plus an explain of the query
    – Philᵀᴹ
    Oct 24, 2016 at 8:21
  • A LIMIT without ORDER BY can lead to getting random rows.
    – Rick James
    Oct 24, 2016 at 19:15

1 Answer 1

13

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.

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.