SQLite was much slower on many queries AFTER analyze was executed. here are the referenced tables, # of rows, primary key, and additional indexes: Objects = 5495 rows, Primary key=Object_ID, other indexes on Address_ID, Dimension_ID, DisplayFormat_ID, Limit_ID, and Object_ID; DTC_Statuses = 5234 rows, Primary key=DTC_Status_ID, other index on Object_ID; Object_TextTables = 15718 rows, other indexes on Object_ID and TextTable_ID; Object_Transactions = 5747 rows, Primary key=Object_Transaction_ID, other indexes on Equation_ID, Object_ID, Transaction_ID; Equations = 43 rows, Primary key=Equation_ID; Equation_Types = 10 rows, Primary key=Equation_Type_ID; Object_Types = 5 rows, Primary key=Object_Type_ID; DisplayFormats = 7 rows, Primary key=DisplayFormat_ID.
I'm not sure where it is getting 1000000 rows! Vance on Apr 15, 2013, Simon Slavin <slav...@bigfraud.org> wrote: > > >On 15 Apr 2013, at 9:47pm, ven...@intouchmi.com wrote: > >> The integrity check came back with OK. > >Good. > >> My software was not locked up totally. It did finally come back. For some >> reason >executing analyze slow other queries way down. > >I am surprised by anyone reporting that ANALYZE has substancially slowed down >any >operation. Please excuse me because I have trouble understanding English >sometimes. > Can you verify for us: > >A) The slow-down you're talking about. Are you saying that other operations >ran slowly >while you were doing ANALYZE, or that the SELECTs ran more slowly after the >ANALYZE >than they did before it ? > >B) Reading the results of the EXPLAIN QUERY PLAN after the ANALYZE, you should >see >approximate row counts like > >> 0|5|3|SCAN TABLE DisplayFormats (~7 rows) > >This means that the table DisplayFormats has about 7 rows. If you take this >as meaning >that it has between 1 and 70 rows, are these rowcounts correct as far as you >know, >or has it completely miscounted a table which actually has thousands of rows >as having >just 1 row ? > >C) Are these EXPLAIN QUERY PLANs you're posting all for the same SELECT >statement >or are you switching from one to another ? > >Also I see in your latest EQP > >> 0|1|1|SCAN TABLE DTC_Statuses (~1000000 rows) > >Obviously, scanning a million rows is something you want to avoid. Have you >created >an appropriate index ? > >Simon >_______________________________________________ >sqlite-users mailing list >sqlite-users@sqlite.org >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
