On Mon, Apr 15, 2013 at 10:04 PM, Dominique Devienne <ddevie...@gmail.com>wrote:
> On Mon, Apr 15, 2013 at 9:45 PM, <ven...@intouchmi.com> wrote: > >> Igor, >> >> As a follow up, I added the other two indexes with no real inprovement. >> Here is the new explain query plan: >> 0|0|6|SEARCH TABLE Object_Types USING COVERING INDEX >> sqlite_autoindex_Object_Types_1 (Object_Type=?) (~1 rows) >> 0|1|1|SCAN TABLE DTC_Statuses (~1000000 rows) >> 0|2|0|SEARCH TABLE Objects USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) >> 0|3|7|SEARCH TABLE DisplayFormats USING INTEGER PRIMARY KEY (rowid=?) (~1 >> rows) >> 0|4|2|SEARCH TABLE Object_TextTables USING INDEX >> Object_TextTables_1_index (Object_ID=?) (~10 rows) >> 0|5|3|SEARCH TABLE Object_Transactions USING INDEX >> Object_Transactions_2_index (Object_ID=?) (~10 rows) >> 0|6|4|SEARCH TABLE Equations USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) >> 0|7|5|SEARCH TABLE Equation_Types USING INTEGER PRIMARY KEY (rowid=?) (~1 >> rows) >> > > No clue if it matters, but maybe you could gather stats on the tables, and > see if that helps? --DD > http://www.sqlite.org/lang_analyze.html > The end of 5.2 in http://www.sqlite.org/optoverview.html suggests it can make a difference. Especially since the QUERY PLAN shows 1,000,000, and you wrote there are 5234 rows only. As 7.0 states, you could also use "covering indexes" (an index will all the columns your query is accessing indexed) for your small lookup tables, to avoid an indirection back from the index to the table. SELECT [Objects].[Object_ID], [Object_TextTables].[Lower_Limit], [Object_TextTables].[Upper_Limit], [Object_TextTables].[TextTable_ID], [DTC_Statuses].[DTC_Status_ID], [DTC_Statuses].[Env_Data], [DTC_Statuses].[Env_Offset], [DTC_Statuses].[DTCs_Follow], [DTC_Statuses].[Upper_Limit], [DTC_Statuses].[Lower_Limit], [DTC_Statuses].[Physical_Value], [Object_Transactions].[Element_Offset], [Equation_Types].[Equation_Type], [Equations].[BitMask], [Equations].[ByteSpecific], [Equations].[ObjectSpecific], [Equations].[InternalEncoder], [Equations].[Equation], [Objects].[Object_Size], [Objects].[Signed], [Objects].[Visible], [Object_Types].[Object_Type], [DisplayFormats].[DisplayFormat] FROM ((((((Objects INNER JOIN DTC_Statuses ON [Objects].[Object_ID]=[DTC_Statuses].[Object_ID]) INNER JOIN Object_TextTables ON [Objects].[Object_ID]=[Object_TextTables].[Object_ID]) INNER JOIN Object_Transactions ON [Objects].[Object_ID]=[Object_Transactions].[Object_ID]) INNER JOIN Equations ON [Object_Transactions].[Equation_ID]=[Equations].[Equation_ID]) INNER JOIN Equation_Types ON [Object_Transactions].[Equation_Type_ID]=[Equation_Types].[Equation_Type_ID]) INNER JOIN Object_Types ON [Objects].[Object_Type_ID]=[Object_Types].[Object_Type_ID]) INNER JOIN DisplayFormats ON [Objects].[DisplayFormat_ID]=[DisplayFormats].[DisplayFormat_ID] WHERE ((([Object_Types].[Object_Type])="DTC")) _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
