What kind of index for orderby/where query in SQL?

Question

I'd like to run a query on an SQLite database which looks like

SELECT a,b,c,d FROM data WHERE a IN (1,2,3) ORDER BY b,c

What kind/order of index should I use to enable SQLite (or maybe later MySQL) to do this fast? How can I easily check if the query is being enhanced by an index (i.e. how to interpret EXPLAIN)? Will SQLite be faster if I include d in the index?

EDIT: Here are the characteristics of the table:

• 10.000.000 rows
• 60 distinct a
• 6.000.000 distinct b
• 2.000 distinct c
• no constraints
• the table is my personal analytics data; it is written only once and then only read

PS: Is there a reference where I can learn when SQLite/MySQL can use indices?

Answer 1

If, and only if, IN (1,2,3) is a constant list (always the same values) you can use a partial index like so:

CREATE INDEX so ON data (b,c) WHERE a IN (1,2,3)

Then running your query gives this plan (explain query plan select...):

0|0|0|SCAN TABLE data USING INDEX so
0|0|0|EXECUTE LIST SUBQUERY 1

Note: no ORDER BY operation.

As a counter test, let's drop the index and replace it like so:

CREATE INDEX so ON data (a,b,c);

The new execution plan is:

0|0|0|SEARCH TABLE data USING INDEX so (a=?)
0|0|0|EXECUTE LIST SUBQUERY 1
0|0|0|USE TEMP B-TREE FOR ORDER BY

You see the sort operation now?

I haven't generated any meaningful test data (just an empty table) to verify the execution speed improvement. But I guess you should see it right away after creating the index.

Also note that partial indexes are only supported since SQLite 3.8.0 (released 2013-08-26)