CODEWITHSUNDEEP
sqldatabasesqliteindexingrdbms
Galaxy
Galaxy

Reputation: 2012

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

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 = ?

Upvotes: 1

Views: 1362

Answers (3)

wierob
wierob

Reputation: 4359

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 ..."

Upvotes: 2

Juergen
Juergen

Reputation: 12728

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.

Upvotes: 0

James Anderson
James Anderson

Reputation: 27478

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.

Upvotes: 0

Related Questions