>Ok then, show the result of prepending EXPLAIN QUERY PLAN to your statement. >-- >Igor Tandetnik
First of all thanks for bearing with me :) functions TZB_MATCHRECURSIVE(int,int) - disabled for this test - always return 1. applies filter recursively TZB_ISCHILD(int) - bitmask check TZB_MATCHDIM(int,text) - disabled for this test - always return 1. does weird stuff with text parameter You probably by now realized the table forms tree of items. Parent links to IDI and this way tree structure is formed. parent parameter is for testing set to 0 - which is root of tree (query should return top-most folders) in this query all ItemsME.Status and ItemsME.Points are NULL. there is no entry in ItemsME for directories and there are no "files" in root 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]; plan "SEARCH TABLE IndexME USING AUTOMATIC COVERING INDEX (Parent=?) (~5 rows)" "SEARCH TABLE ItemsME USING AUTOMATIC COVERING INDEX (IDR=?) (~5 rows)" "USE TEMP B-TREE FOR ORDER BY" runtime ~2seconds 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]; plan "SEARCH TABLE IndexME USING AUTOMATIC COVERING INDEX (Parent=?) (~5 rows)" "SEARCH TABLE ItemsME USING AUTOMATIC COVERING INDEX (IDR=?) (~5 rows)" "USE TEMP B-TREE FOR ORDER BY" runtime ~160miliseconds test 3. (TZB_MATCHDIM replaced with LENGTH to prove it's not my func) results equal to 1) _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
