Hi, Celemens I created the requested log data by running the same operations in my application, once compiled with the 3.7.15.1 version (OK) and once with the 3.7.16.2 (BAD performance). The query plans are quite different.
1. --- The query my application runs for this test INSERT INTO md_fts_core (group_oid,tag_oid,file_oid,lang,data) SELECT g.oid, d.tag_oid, d.oid, d.lang, d.tdata from md_tag_data d INNER JOIN md_tag t ON d.tag_oid = t.oid INNER JOIN md_tag_group g ON t.group_oid = g.oid INNER JOIN md_fts_core_tag ct ON ct.type = 0 AND ct.oid = d.tag_oid WHERE d.oid IN (1,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,3 4,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59 ,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84, 85,86,87,88,89,90,91,92,93,94,95,96,97,98) AND d.tag_oid IN (2157,7309,16265,16579) UNION SELECT g.oid, d.tag_oid, d.oid, d.lang, group_concat(d.tdata,' ') FROM md_tag_data d INNER JOIN md_tag t ON d.tag_oid = t.oid INNER JOIN md_tag_group g ON t.group_oid = g.oid INNER JOIN md_fts_core_tag ct ON ct.type = 1 AND ct.oid = d.tag_oid WHERE d.oid IN (1,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,3 4,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59 ,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84, 85,86,87,88,89,90,91,92,93,94,95,96,97,98) AND d.tag_oid IN (2157,7309,16265,16579) GROUP BY d.tag_oid,d.oid 2. --- Plan of 3.7.15.1 (GOOD) SEARCH TABLE md_tag_data AS d USING INDEX idx_md_tag_data_tag_oid (tag_oid=?) (~65 rows) EXECUTE LIST SUBQUERY 2 EXECUTE LIST SUBQUERY 2 SEARCH TABLE md_fts_core_tag AS ct USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) SEARCH TABLE md_tag AS t USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) SEARCH TABLE md_tag_group AS g USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) SEARCH TABLE md_tag_data AS d USING INDEX idx_md_tag_data_tag_oid (tag_oid=?) (~65 rows) EXECUTE LIST SUBQUERY 3 EXECUTE LIST SUBQUERY 3 SEARCH TABLE md_fts_core_tag AS ct USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) SEARCH TABLE md_tag AS t USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) SEARCH TABLE md_tag_group AS g USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) USE TEMP B-TREE FOR GROUP BY COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION) 3. --- Plan of 3.7.16.2 (BAD) SEARCH TABLE md_fts_core_tag AS ct USING INTEGER PRIMARY KEY (rowid=?) (~4 rows) EXECUTE LIST SUBQUERY 2 SEARCH TABLE md_tag AS t USING INTEGER PRIMARY KEY (rowid=?) (~4 rows) EXECUTE LIST SUBQUERY 2 SEARCH TABLE md_tag_group AS g USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) SEARCH TABLE md_tag_data AS d USING INDEX idx_md_tag_data_tag_oid (tag_oid=?) (~2 rows) EXECUTE LIST SUBQUERY 2 EXECUTE LIST SUBQUERY 2 SEARCH TABLE md_fts_core_tag AS ct USING INTEGER PRIMARY KEY (rowid=?) (~4 rows) EXECUTE LIST SUBQUERY 3 SEARCH TABLE md_tag AS t USING INTEGER PRIMARY KEY (rowid=?) (~4 rows) EXECUTE LIST SUBQUERY 3 SEARCH TABLE md_tag_group AS g USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) SEARCH TABLE md_tag_data AS d USING INDEX idx_md_tag_data_tag_oid (tag_oid=?) (~2 rows) EXECUTE LIST SUBQUERY 3 EXECUTE LIST SUBQUERY 3 USE TEMP B-TREE FOR GROUP BY COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION) _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
