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)
Vance
on Apr 15, 2013, ven...@intouchmi.com wrote:
>
>Thanks Igor!
>
>I did create indexes for the SQLite DB version based on the indexes in the
>MS Access DB.
>Object_Texttables does have an index on Object_ID
>and Object_Transactions also has an index on - Object_ID.
>
>Just to double check, my index declarations are:
>CREATE INDEX IF NOT EXISTS
>"Object_TextTables_1_index" ON
>"Object_TextTables" ("Object_ID");
>and
>CREATE INDEX IF NOT EXISTS
>"Object_Transactions_2_index" ON
>"Object_Transactions" ("Object_ID");
>
>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)
>
>I appreciate the time you spending on this!
>
>Vance
>
>
>on Apr 15, 2013, Igor Tandetnik <i...@tandetnik.org> wrote:
>>
>>On 4/15/2013 2:20 PM, ven...@intouchmi.com wrote:
>>> here is the output from explain:
>>
>>I asked about EXPLAIN QUERY PLAN, not EXPLAIN.
>>--
>>Igor Tandetnik
>>
>>_______________________________________________
>>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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
