On 4/15/2013 3:27 PM, [email protected] wrote:
Here is the result of the explain query plan: 0|0|1|SCAN TABLE DTC_Statuses (~1000000 rows) 0|1|0|SEARCH TABLE Objects USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) 0|2|6|SEARCH TABLE Object_Types 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)
Try adding an index on DTC_Statuses(Object_ID). You want the SCAN to be on Object_Types (because it's small and further limited by the WHERE clause), and everything else done via SEARCH (which requires a primary key or a suitable index). The only missing link is a way to search DTC_Statuses given an Object_ID.
-- Igor Tandetnik _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
