sqlite determine if index is used

Question

1. How to understand if created index used when select query run?

2. Specific example for my task:

Index:

  events (page,notuniqueid,type,timestamp);

SQL statement:

  SELECT * 
  FROM events 
  WHERE type = 'comment' AND TIMESTAMP > $time AND PAGE = '$page' 
  LIMIT 1

Will index be used at this query?

Answer 1

To find out how a query is executed, run it with EXPLAIN QUERY PLAN:

> EXPLAIN QUERY PLAN SELECT * from events WHERE type='comment' AND TIMESTAMP > $time AND PAGE = '$page' LIMIT 1
0|0|0|SEARCH TABLE events USING INDEX MyLittleIndex (page=?)

In this case, only the first column of the index can be used because there is no lookup on the notuniqueuid column.

A better index (for this query) would be on (page,comment,timestamp).

(See The SQLite Query Planner.)