Have the following query: INSERT INTO A3Test7D4_J (PATIENT_ID, ENTRY_ID, READ_CODE, TERM_TEXT, ADDED_DATE, START_DATE, NUMERIC_VALUE, TYPE_SPECIFIC_INFO) SELECT E.PATIENT_ID, E.ENTRY_ID, E.READ_CODE, E.TERM_TEXT, E.ADDED_DATE, E.START_DATE, EA.NUMERIC_VALUE, EA.TYPE_SPECIFIC_INFO FROM A3Test7D4_E E INNER JOIN ENTRY_ATTRIBUTES EA ON (EA.ENTRY_ID = +E.ENTRY_ID) WHERE EA.ATTRIBUTE_TYPE = 101 ORDER BY E.PATIENT_ID ASC, E.ENTRY_ID ASC, EA.NUMERIC_VALUE ASC
And as it was quite slow (50 secs) I did an analyze on the table ENTRY_ATTRIBUTES and after that the query was much faster, less than one second. Now, what I don't understand about this is that the query plan is still the same: 0--0--0--SCAN TABLE A3Test7D4_E AS E (~18491 rows) <--> 0--1--1--SEARCH TABLE ENTRY_ATTRIBUTES AS EA USING COVERING INDEX IDX_ENTRY_ATTRIBUTES_ATTRIBUTE_TYPE_ENTRY_ID_TYPE_SPECIFIC_INFO_NUMERIC_VALUE (ATTRIBUTE_TYPE=? AND ENTRY_ID=?) (~2 rows) <--> 0--0--0--USE TEMP B-TREE FOR ORDER BY And also the data in sqlite_stat1 is still the same: IDX_ENTRY_ATTRIBUTES_NUMERIC_VALUE 1389416 176 IDX_ENTRY_ATTRIBUTES_TYPE_SPECIFIC_INFO 1389416 9 IDX_ENTRY_ATTRIBUTES_ENTRY_ID 1389416 2 IDX_ENTRY_ATTRIBUTES_ATTRIBUTE_TYPE 1389416 198488 IDX_ENTRY_ATTRIBUTES_ATTRIBUTE_TYPE_ENTRY_ID_TYPE_SPECIFIC_INFO_NUMERIC_VALUE 1389416 198488 2 2 1 The data in the table is the same as well. The other table involved A3Test7D4_E is exactly the same as well. So why runs this query so much faster after running the analyze? RBS _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
