OK, thanks. I can see it makes sense to run analyze after the DB is completed. I take it I can run the analyze before the commit, just as any other action query.
This is the output from SELECT * FROM sqlite_stat1; tbl idx stat ------------------- ENTRY IDX14ENTRY 3121645 104 ENTRY IDX13ENTRY 3121645 208110 ENTRY IDX12ENTRY 3121645 124 ENTRY IDX11ENTRY 3121645 37611 ENTRY IDX10ENTRY 3121645 31 ENTRY IDX9ENTRY 3121645 251 ENTRY IDX8ENTRY 3121645 312165 ENTRY IDX7ENTRY 3121645 29 ENTRY IDX6ENTRY 3121645 433 ENTRY IDX5ENTRY 3121645 429 ENTRY IDX4ENTRY 3121645 6 ENTRY IDX3ENTRY 3121645 217 ENTRY IDX2ENTRY 3121645 51175 ENTRY IDX1ENTRY 3121645 51175 ENTRYLINK IDX3ENTRYLINK 151096 12 ENTRYLINK IDX2ENTRYLINK 151096 3 ENTRYLINK IDX1ENTRYLINK 151096 75548 ENTRYLINK IDX0ENTRYLINK 151096 1 PHONELINK IDX4PHONELINK 25817 5164 PHONELINK IDX3PHONELINK 25817 12909 PHONELINK IDX2PHONELINK 25817 2 PHONELINK IDX1PHONELINK 25817 2 PHONE IDX2PHONE 18438 28 PHONE IDX1PHONE 18438 3 REMINDER IDX6REMINDER 30746 156 REMINDER IDX5REMINDER 30746 2 REMINDER IDX4REMINDER 30746 12 REMINDER IDX3REMINDER 30746 7687 REMINDER IDX2REMINDER 30746 10 REMINDER IDX1REMINDER 30746 10249 ADDRESSLINK IDX2ADDRESSLINK 25793 2 ADDRESSLINK IDX1ADDRESSLINK 25793 2 ADDRESS IDX7ADDRESS 17399 153 ADDRESS IDX6ADDRESS 17399 61 ADDRESS IDX5ADDRESS 17399 28 ADDRESS IDX4ADDRESS 17399 2 ADDRESS IDX3ADDRESS 17399 27 ADDRESS IDX2ADDRESS 17399 11 ADDRESS IDX1ADDRESS 17399 13 PATIENT IDX8PATIENT 25324 9 PATIENT IDX7PATIENT 25324 5 PATIENT IDX6PATIENT 25324 37 PATIENT IDX5PATIENT 25324 2 PATIENT IDX4PATIENT 25324 2 PATIENT IDX3PATIENT 25324 6331 PATIENT IDX2PATIENT 25324 3618 PATIENT IDX1PATIENT 25324 3166 AUTHORISATION IDX9AUTHORISATION 259812 109 AUTHORISATION IDX8AUTHORISATION 259812 104 AUTHORISATION IDX7AUTHORISATION 259812 18 AUTHORISATION IDX6AUTHORISATION 259812 528 AUTHORISATION IDX5AUTHORISATION 259812 890 AUTHORISATION IDX4AUTHORISATION 259812 1556 AUTHORISATION IDX3AUTHORISATION 259812 62 AUTHORISATION IDX2AUTHORISATION 259812 2 AUTHORISATION IDX1AUTHORISATION 259812 2260 AUTHORISATION IDX0AUTHORISATION 259812 1 ENCOUNTER IDX4ENCOUNTER 639545 30 ENCOUNTER IDX3ENCOUNTER 639545 13 ENCOUNTER IDX2ENCOUNTER 639545 16399 ENCOUNTER IDX1ENCOUNTER 639545 10316 ENTRY_ATTRIBUTE IDX5ENTRY_ATTRIBUTE 289381 54 ENTRY_ATTRIBUTE IDX4ENTRY_ATTRIBUTE 289381 15 ENTRY_ATTRIBUTE IDX3ENTRY_ATTRIBUTE 289381 2 ENTRY_ATTRIBUTE IDX2ENTRY_ATTRIBUTE 289381 36173 ENTRY_ATTRIBUTE IDX1ENTRY_ATTRIBUTE 289381 20 DISCONTINUATION IDX3DISCONTINUATION 93204 7170 DISCONTINUATION IDX2DISCONTINUATION 93204 2 DISCONTINUATION IDX1DISCONTINUATION 93204 8 SENSITIVITY IDX4SENSITIVITY 441 2 SENSITIVITY IDX3SENSITIVITY 441 89 SENSITIVITY IDX2SENSITIVITY 441 89 SENSITIVITY IDX1SENSITIVITY 441 441 EXTENDED_TEXT IDX1EXTENDED_TEXT 15424 2 EXTENDED_TEXT IDX0EXTENDED_TEXT 15424 2571 ISSUE IDX6ISSUE 915819 61 ISSUE IDX5ISSUE 915819 5 ISSUE IDX4ISSUE 915819 4 ISSUE IDX3ISSUE 915819 2313 ISSUE IDX2ISSUE 915819 172 ISSUE IDX1ISSUE 915819 38160 REFERRAL IDX6REFERRAL 11046 16 REFERRAL IDX5REFERRAL 11046 16 REFERRAL IDX4REFERRAL 11046 16 REFERRAL IDX3REFERRAL 11046 1381 REFERRAL IDX2REFERRAL 11046 850 REFERRAL IDX1REFERRAL 11046 614 RBS -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 01 December 2006 21:01 To: sqlite-users@sqlite.org Subject: Re: [sqlite] When to run analyze? "RB Smissaert" <[EMAIL PROTECTED]> wrote: > Having a reasonably big SQLite database, 21 tables, one table with some > millions of rows, overall file size about 1.3 Gb. This table will only get > SELECT statements once it has been created. I have now run analyze on this > file and it does speed queries up indeed. > Do I run analyze after the table has been completed (all tables created and > populated) or should I run analyze before the creation of any tables as I > read somewhere? > BTW, I would be interested in seeing the output of SELECT * FROM sqlite_stat1; After you have run analyze. And also the output of running sqlite3_analyzer on your database, if you are willing to share it. This kind of data helps me to see what people are putting into SQLite database, which in turn helps me to tune it better. -- D. Richard Hipp <[EMAIL PROTECTED]> ---------------------------------------------------------------------------- - To unsubscribe, send email to [EMAIL PROTECTED] ---------------------------------------------------------------------------- - ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
