Does a multi-column index work for single column selects too?

Question

I've got (for example) an index:

CREATE INDEX someIndex ON orders (customer, date);

Does this index only accelerate queries where customer and date are used or does it accelerate queries for a single-column like this too?

SELECT * FROM orders WHERE customer > 33;

I'm using SQLite.

---

If the answer is yes, why is it possible to create more than one index per table?

---

Yet another question: How much faster is a combined index compared with two separat indexes when you use both columns in a query?

Answer 1

I'm pretty sure this will work, yes - it does in MS SQL Server anyway.

However, this index doesn't help you if you need to select on just the date, e.g. a date range. In that case, you might need to create a second index on just the date to make those queries more efficient.

Marc

Answer 2

marc_s has the correct answer to your first question. The first key in a multi key index can work just like a single key index but any subsequent keys will not.

As for how much faster the composite index is depends on your data and how you structure your index and query, but it is usually significant. The indexes essentially allow Sqlite to do a binary search on the fields.

Using the example you gave if you ran the query:

SELECT * from orders where customer > 33 && date > 99

Sqlite would first get all results using a binary search on the entire table where customer > 33. Then it would do a binary search on only those results looking for date > 99.

If you did the same query with two separate indexes on customer and date, Sqlite would have to binary search the whole table twice, first for the customer and again for the date.

So how much of a speed increase you will see depends on how you structure your index with regard to your query. Ideally, the first field in your index and your query should be the one that eliminates the most possible matches as that will give the greatest speed increase by greatly reducing the amount of work the second search has to do.

For more information see this: http://www.sqlite.org/optoverview.html