Search code examples
mysqlsqlindexingwhere-in

How to force Mysql to use primary Index in IN clause...?

    Query1. SELECT * FROM traffic WHERE id IN (1904817,1904699,1882859);

    Query2. SELECT * FROM traffic WHERE id IN 
            (SELECT id FROM data_table where condition=1);

Here traffic table having nearly a million record, and id is the primary key.

Query1 using index id

where as in Query2 it is not using the index .

what to be done to run Query2 efficently... by using index...

Solution

  • Use inner join instead of IN and make another index on condition column:

    select t.* from traffic t
inner join data_table d on t.id = d.id and d.contition = 1