Why SQLite index is not speed up query?

Question

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

That EXISTS subquery seems a bit superfluous since you already have that join, no? — Konerak
– Konerak, Commented Mar 23, 2012 at 16:39
StatTable.Key_ID and KeysTable.ID had same datatypes: [ID] integer PRIMARY KEY AUTOINCREMENT NOT NULL //--- [Key_ID] integer NOT NULL — Dmitriy
– Dmitriy, Commented Mar 23, 2012 at 16:42
@Dmitriy: Then (still wondering how that is not producing an error), you can remove the EXISTS subquery. You already have those conditions in the Join. — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Mar 23, 2012 at 16:49

ypercubeᵀᴹ · Accepted Answer · 2012-03-23 16:53:27Z

4

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)

answered Mar 23, 2012 at 16:53

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

ypercubeᵀᴹ Over a year ago

I'm still buffled as to what this is trying to accomplish. You are inserting some data in the KeysTable but these data are already there... What's the point of having identical rows in the table?

Dmitriy Over a year ago

Thanks! Still wondering why I use exists before :) (maybe before I have another subquery - don't know). Exists is useless, you are right.

ypercubeᵀᴹ Over a year ago

@Dmitriy: Can you explain what the Insert statement is trying to do? What's the use of it?

Dmitriy Over a year ago

This insert looks like waste because I cut some fields from table :).

Collectives™ on Stack Overflow

Why SQLite index is not speed up query?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related