In SQLite3, will this select statement benefit from two indexes?

Question

I am using a DBD::SQLite in memory database. I have defined the following indexes for a table:

CREATE INDEX x ON ss (a, b);
CREATE INDEX y ON ss (c, d, e, o);

Will the following select statement use these two indexes?

SELECT f, g
FROM ss
WHERE a = ? AND b = ? AND c = ? AND d = ? AND e = ?

And, I should only make indexes on those columns in the where clause?

---

I ask this just because I want to run more SELECT with minimal INDEX.

SELECT f, g FROM ss WHERE o = ?
SELECT f, g FROM ss WHERE a = ? AND b = ?
SELECT f, g FROM ss WHERE c = ? AND d = ? AND e = ?
SELECT f, g FROM ss WHERE c = ? AND d = ? AND o = ?
SELECT f, g FROM ss WHERE a = ? AND b = ? AND c = ? AND d = ? AND e = ?

Answer 1

Could be wrong but given the small footprint of sqlite I would be very surprised if it ever used more than 1 index for each tablein a (sub)Select.

Monster databases like ORACLE and DB2 only recently started to use more than one index per table per subselect and then only rarely.

Answer 2

Use EXPLAIN QUERY PLAN (http://sqlite.org/lang_explain.html) to see which indexes are used.

EXPLAIN QUERY PLAN
SELECT f, g
FROM ss
WHERE a = ? AND b = ? AND c = ? AND d = ? AND e = ?

Reuslt in something like

"0","0","TABLE ss WITH INDEX ..."

Answer 3

As much as I remember the SQLite documentation, SQLite always uses only one index.

So you are bound to fail here. But you could create one index containing all relevant fields (though this might create additional memory consumption for index size and will slow down inserts and updates).

Correction: In the newest documentation, it says that the optimizer tries to use "at least one index" see the optimizer docu. It seams, that it was upgraded a little -- but still it is not clear when it uses multiple indizes. So you have to use "EXPLAIN QUERY PLAN" as statet by wierob.