CODEWITHSUNDEEP
sqlperformancesqliteindexing
Dmitriy
Dmitriy

Reputation: 664

Why SQLite index is not speed up query?

I wrote this query:

INSERT INTO KeysTable (KeyText, Id)
SELECT KeyText as BKT, KeysTable.ID as CID FROM KeysTable
INNER JOIN StatTable ON KeysTable.ID = StatTable.Key_ID
WHERE StatTable.StatCommandCode = 4 AND 
EXISTS (SELECT 1 FROM StatTable WHERE StatCommandCode = 4 AND StatTable.Key_ID = CID);

I know that removing the condition

AND StatTable.Key_ID = CID

would make the query very fast. Also if I replace it with 

AND StatTable.Key_ID = 444 // (444 - random static number)

the query will be very fast too. Both the columns in this condition are indexed:

CREATE INDEX IF NOT EXISTS StatsIndex ON StatTable (Key_ID);

and in KeysTable the ID column is primary key. Why doesn't the index improve perfomance in this case?

Thanks for answers and sorry for my bad english :(.

Upvotes: 1

Views: 276

Answers (1)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115520

If there is no CID column in any of the two tables, then the EXISTS subquery is useless. Rewrite the statement as:

INSERT INTO KeysTable (KeyText, Id)
  SELECT KeyText
       , KeysTable.ID  
  FROM KeysTable
    INNER JOIN StatTable 
      ON KeysTable.ID = StatTable.Key_ID
  WHERE StatTable.StatCommandCode = 4

If it is still slow, you can try adding an index on (StatCommandCode, Key_ID)

Upvotes: 4

Related Questions