Given this table:
CREATE TABLE AMorb37F6_E
([PATIENT_ID] INTEGER,
[ENTRY_ID] INTEGER PRIMARY KEY,
[READ_CODE] TEXT,
[ADDED_DATE] TEXT,
[START_DATE] TEXT)
And these indexes:
IDX10$ENTRY$PATIENT_ID
IDX11$ENTRY$TERM_TEXT
IDX12$ENTRY$READ_CODE$ADDED_DATE
IDX13$ENTRY$READ_CODE$START_DATE
IDX14$ENTRY$READ_CODE$PROBLEM_ID
IDX15$ENTRY$READ_CODE$ADDED_DATE$PROBLEM_ID
IDX16$ENTRY$READ_CODE$ADDED_DATE$START_DATE$PROBLEM_ID
IDX2$ENTRY$ADDED_BY
IDX3$ENTRY$READ_CODE
IDX4$ENTRY$ENCOUNTER_ID
IDX5$ENTRY$ADDED_DATE
IDX6$ENTRY$UPDATED_DATE
IDX7$ENTRY$START_DATE
IDX8$ENTRY$PROBLEM_ID
IDX9$ENTRY$ENTRY_FLAGS
And this query:
SELECT
E.PATIENT_ID,
E.ENTRY_ID,
E.READ_CODE,
E.ADDED_DATE,
E.START_DATE
FROM
ENTRY E
WHERE
E.READ_CODE GLOB 'G2*' AND
(NOT E.DORMANT_FLAG = 1)
ORDER BY
E.PATIENT_ID ASC, E.ENTRY_ID ASC
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.
What are the general rules as to how SQLites picks from the available
indexes?
RBS
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
