Ignore this as I found out what the trouble was. I dropped 2 indices from the table ADDRESSLINK as they were on fields where the values were nearly all the same. Forgot now what the technical term for it is, but it solved it all and now very fast. Great software this SQLite!
RBS -----Original Message----- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: 26 November 2006 10:04 To: sqlite-users@sqlite.org Subject: [sqlite] slow query 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] ---------------------------------------------------------------------------- - ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
