Mira Suk <mira....@centrum.cz> wrote: > test 1. > ------------------------------------ > query > SELECT [IndexME].[IDI], [IndexME].[Status], [IndexME].[Icon], [IndexME].[Text] > FROM [IndexME] LEFT OUTER JOIN [ItemsME] > ON [ItemsME].[IDR] = [IndexME].[IDI] WHERE > [IndexME].[Parent] = ?1 AND > (TZB_MATCHRECURSIVE([IndexME].[IDI], [IndexME].[Status]) OR > (TZB_ISCHILD([IndexME].[Status]) AND TZB_MATCHDIM([ItemsME].[Status], > [ItemsME].[Points]))) > ORDER BY [IndexME].[Order]; > > test 2. (TZB_MATCHDIM removed) > ------------------------------------ > query > SELECT [IndexME].[IDI], [IndexME].[Status], [IndexME].[Icon], [IndexME].[Text] > FROM [IndexME] LEFT OUTER JOIN [ItemsME] > ON [ItemsME].[IDR] = [IndexME].[IDI] WHERE > [IndexME].[Parent] = ?1 AND > (TZB_MATCHRECURSIVE([IndexME].[IDI], [IndexME].[Status]) OR > (TZB_ISCHILD([IndexME].[Status]))) > ORDER BY [IndexME].[Order];
The difference is that #2 mentions only one field from ItemsME, namely IDR. The value of that field comes from the index, the table itself doesn't need to be read at all. It's not even clear why #2 bothers to join with ItemsME at all - it's a no-op. #1 uses more fields from ItemsME, so it needs to actually look up and read records from that table. -- Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
