CODEWITHSUNDEEP
sqlsqlite
smor
smor

Reputation: 161

Improving an SQLite Query

I have an event table has around 100k rows in it. The following complex query on an "event" table and would like to have your opinion on how to optimize this query...

SELECT id FROM event 
NATURAL JOIN (
    SELECT subj_id, max(timestamp) AS timestamp
    FROM event WHERE (
        timestamp >= 1342052128597 AND timestamp <= 9223372036854775807
        AND NOT subj_interpretation = 46))
    GROUP BY subj_id)
GROUP BY subj_id
ORDER BY  
timestamp DESC

The query performs here in 0.06-0.07 seconds. Any ideas what good indicies and/or restructures of the query would be.

Currently I am using:

CREATE INDEX event_subj_id ON event(subj_id, timestamp, subj_interpretation)

And the query plan looks as follows:

1 0 0 SCAN TABLE event USING COVERING INDEX event_subj_id (~27777 rows)
0 0 1 SCAN SUBQUERY 1 (~100 rows)
0 1 0 SEARCH TABLE event USING INDEX event_subj_id (subj_id=? AND timestamp=?) (~9 rows)
0 0 0 USE TEMP B-TREE FOR GROUP BY
0 0 0 USE TEMP B-TREE FOR ORDER BY

Upvotes: 2

Views: 155

Answers (1)

CL.
CL.

Reputation: 180162

You can optimize your second query by removing the DISTINCT, because the GROUP BY subj_id already ensures that there cannot be duplicate records:

SELECT id,
       subj_id,
       max(timestamp) AS timestamp
FROM event
WHERE timestamp BETWEEN 1342055894621 AND 9223372036854775807
  AND subj_interpretation != 46
GROUP BY subj_id
ORDER BY timestamp

The index itself is already optimal for the GROUP BY/timestamp/subj_interpretation lookup. However, if you add the id column to it, SQLite can use it as a covering index, which removes the need to look up any record in the events table itself, which might actually double the performance:

0 0 0 SCAN TABLE event USING COVERING INDEX event_subj_id_plus_id
0 0 0 USE TEMP B-TREE FOR ORDER BY

Upvotes: 1

Related Questions