Although SQLite seems very fast, I now have come across a query that runs extremely slow and I would be interested why this is:
There are 3 tables involved, all fairly small, some 25000 rows: PATIENT, ADDRESS and ADDRESSLINK All the relevant fields are indexed. This is the query: SELECT P.PATIENT_ID, P.OLD_EXTERNAL_NO, P.FORENAME_1, P.SURNAME, P.DATE_OF_BIRTH, P.GENDER_TYPE, A.ADDRESS_LINE_2, P.REGISTERED_GP FROM PATIENT P LEFT JOIN ADDRESSLINK AL ON (P.PATIENT_ID = AL.FOREIGN_ID AND AL.ADDRESS_TYPE = 1 AND AL.ADDRESS_LINK_TYPE = 2) LEFT JOIN ADDRESS A ON (A.ADDRESS_ID = AL.ADDRESS_ID) WHERE P.MAIN_REG_TYPE = 1 AND A.ADDRESS_LINE_1 LIKE '%ave%' ORDER BY P.PATIENT_ID ASC These are the table definitions, where the numbers are the rootpage: table ADDRESS ADDRESS 2 CREATE TABLE [ADDRESS] ([ADDRESS_ID] INTEGER, [ADDRESS_LINE_1] TEXT, [POSTCODE] TEXT, [UPDATED_DATE] INTEGER, [ADDRESS_LINE_2] TEXT, [ADDRESS_LINE_3] TEXT, [ADDRESS_LINE_4] TEXT, [ADDRESS_LINE_5] TEXT) index IDX0ADDRESS ADDRESS 312 CREATE UNIQUE INDEX IDX0ADDRESS ON ADDRESS(ADDRESS_ID) index IDX1ADDRESS ADDRESS 362 CREATE INDEX IDX1ADDRESS ON ADDRESS(ADDRESS_LINE_1) index IDX2ADDRESS ADDRESS 411 CREATE INDEX IDX2ADDRESS ON ADDRESS(POSTCODE) index IDX3ADDRESS ADDRESS 493 CREATE INDEX IDX3ADDRESS ON ADDRESS(UPDATED_DATE) index IDX4ADDRESS ADDRESS 551 CREATE INDEX IDX4ADDRESS ON ADDRESS(ADDRESS_LINE_2) index IDX5ADDRESS ADDRESS 665 CREATE INDEX IDX5ADDRESS ON ADDRESS(ADDRESS_LINE_3) index IDX6ADDRESS ADDRESS 753 CREATE INDEX IDX6ADDRESS ON ADDRESS(ADDRESS_LINE_4) index IDX7ADDRESS ADDRESS 856 CREATE INDEX IDX7ADDRESS ON ADDRESS(ADDRESS_LINE_5) table ADDRESSLINK ADDRESSLINK 899 CREATE TABLE [ADDRESSLINK] ([ADDRESS_LINK_ID] INTEGER, [ADDRESS_ID] INTEGER, [FOREIGN_ID] INTEGER, [ADDRESS_TYPE] INTEGER, [ADDRESS_LINK_TYPE] INTEGER) index IDX0ADDRESSLINK ADDRESSLINK 1033 CREATE UNIQUE INDEX IDX0ADDRESSLINK ON ADDRESSLINK(ADDRESS_LINK_ID) index IDX1ADDRESSLINK ADDRESSLINK 1111 CREATE INDEX IDX1ADDRESSLINK ON ADDRESSLINK(ADDRESS_ID) index IDX2ADDRESSLINK ADDRESSLINK 1184 CREATE INDEX IDX2ADDRESSLINK ON ADDRESSLINK(FOREIGN_ID) index IDX3ADDRESSLINK ADDRESSLINK 1253 CREATE INDEX IDX3ADDRESSLINK ON ADDRESSLINK(ADDRESS_TYPE) index IDX4ADDRESSLINK ADDRESSLINK 1319 CREATE INDEX IDX4ADDRESSLINK ON ADDRESSLINK(ADDRESS_LINK_TYPE) table PATIENT PATIENT 313541 CREATE TABLE [PATIENT] ([PATIENT_ID] INTEGER, [USUALLY_SEEN_GP] INTEGER, [ADDED_BY] INTEGER, [UPDATED_BY] INTEGER, [FHSA_ID] INTEGER, [DHA_ID] INTEGER, [REGISTERED_GP] INTEGER, [ROUTE_MARKER_TYPE] INTEGER, [MAIN_REG_TYPE] INTEGER, [GENDER_TYPE] INTEGER, [SURGERY_ID] INTEGER, [ACCEPTANCE_TYPE] INTEGER, [RESIDENTIAL_INSTITUTE] INTEGER, [QUALIFIER_REG_TYPE] INTEGER, [CHI_NUMBER] TEXT, [DATE_OF_BIRTH] INTEGER, [FORENAME_1] TEXT, [FORENAME_2] TEXT, [NHS_NUMBER] TEXT, [SURNAME] TEXT, [DISPENSING_FLAG] INTEGER, [ALIAS] TEXT, [RURAL_MILEAGE] INTEGER, [WALKING_UNITS] INTEGER, [PREVIOUS_NHS_NUMBER] TEXT, [PREVIOUS_SURNAME] TEXT, [UPDATED_DATE] INTEGER, [PREVIOUS_DATE_OF_BIRTH] INTEGER, [MARITAL_STATUS_TYPE] INTEGER, [DATE_OF_DEATH_ACCURACY] INTEGER, [DATE_OF_DEATH] INTEGER, [TITLE_TEXT] TEXT, [REGISTRATION_SOURCE] INTEGER, [REGISTRATION_START_DATE] INTEGER, [REGISTRATION_REMOVAL_SOURCE] INTEGER, [REGISTRATION_REMOVAL_REASON] INTEGER, [REGISTRATION_ACCEPTANCE_DATE] INTEGER, [REGISTRATION_REMOVAL_DATE] INTEGER, [REGISTRATION_CONFIRMED] INTEGER, [REGISTRATION_END_DATE] INTEGER, [SECURE_FLAG] INTEGER, [DORMANT_FLAG] INTEGER, [MEDICAL_RECORDS_FLAG] INTEGER, [MRF_DATE] INTEGER, [LINK_STATUS] INTEGER, [ACCEPTANCE_TRANS_NO] INTEGER, [DEDUCTION_TRANS_NO] INTEGER, [OLD_INTERNAL_NO] INTEGER, [OLD_EXTERNAL_NO] TEXT, [STAFF_MEMBER_FLAG] INTEGER, [PREVIOUS_SURNAME2] TEXT, [MOD_OLD_SERVICE_NO] TEXT, [PREVIOUS_CHI_NUMBER] TEXT, [FOOTPATH_MILES] TEXT, [WATER_MILES] TEXT, [BIRTH_SURNAME] TEXT, [OTHER_FORENAMES] TEXT, [SCRIPT_DISPOSAL_DEST] INTEGER, [FP69_STATUS] INTEGER, [FP69_DATE_AMENDED] INTEGER, [FP69_DATE_DEDUCTED] INTEGER, [MRF_FORWARDED_DATE] INTEGER, [DEDUCTION_REQUESTED] INTEGER, [FP69_EXPIRY_DATE] INTEGER, [FP69_REASON] INTEGER, [FP69_FHSA_NOTES] TEXT, [RESPONSIBLE_HA] INTEGER, [CARER_FLAG] INTEGER, [DEDUCTION_PENDING] INTEGER, [BULK_TRANSFER_DATE] INTEGER) index IDX0PATIENT PATIENT 314906 CREATE UNIQUE INDEX IDX0PATIENT ON PATIENT(PATIENT_ID) index IDX1PATIENT PATIENT 314979 CREATE INDEX IDX1PATIENT ON PATIENT(USUALLY_SEEN_GP) index IDX2PATIENT PATIENT 315044 CREATE INDEX IDX2PATIENT ON PATIENT(REGISTERED_GP) index IDX3PATIENT PATIENT 315109 CREATE INDEX IDX3PATIENT ON PATIENT(MAIN_REG_TYPE) index IDX4PATIENT PATIENT 315173 CREATE INDEX IDX4PATIENT ON PATIENT(DATE_OF_BIRTH) index IDX5PATIENT PATIENT 315255 CREATE INDEX IDX5PATIENT ON PATIENT(NHS_NUMBER) index IDX6PATIENT PATIENT 315370 CREATE INDEX IDX6PATIENT ON PATIENT(UPDATED_DATE) index IDX7PATIENT PATIENT 315452 CREATE INDEX IDX7PATIENT ON PATIENT(REGISTRATION_START_DATE) index IDX8PATIENT PATIENT 315534 CREATE INDEX IDX8PATIENT ON PATIENT(REGISTRATION_REMOVAL_DATE) Thanks for any advice on how this could be improved. RBS ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
