RB Smissaert <[EMAIL PROTECTED]>
wrote:
I get this query plan (explain query plan):
order from detail
----------------------------------------------------
0 0 TABLE ENTRY AS E WITH INDEX
IDX16$ENTRY$READ_CODE$ADDED_DATE$START_DATE$PROBLEM_ID
My question is why doesn't it pick the index:
IDX3$ENTRY$READ_CODE
Not sure, but I would think that is more effective.
It's exactly the same in terms of efficiency. In fact, it is completely
pointless to have two indexes where the column list of one is a strict
prefix of the column list of another. The latter can be used, equally
efficiently, everywhere the former can be used. In some cases the latter
may even be more efficient. Consider:
create table t (a text, b text);
create index ta on t(a);
create index tab on t(a, b);
select a, b from t where a='xyz';
If SQLite chooses to use index ta, then it needs to perform a lookup in
the table (by rowid) to retrieve the value of b. But if it uses index
tab, then the value of b is stored in the index, and the table itself
doesn't need to be consulted at all. So fewer pages to read from disk.
Igor Tandetnik
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
