On Fri, 2014-09-12 at 15:35 -0600, Keith Medcalf wrote: > What happens if you phrase it like this? > > SELECT DISTINCT summary.uid, summary.vcard > FROM folder_id AS summary > LEFT JOIN 'folder_id_email_list' AS email_list > ON email_list.uid = summary.uid > AND email_list.value like 'foo%' > WHERE email_list.value like 'foo%' > OR summary.full_name like 'foo%'; > > Which of course makes the left join only include records already satisfying > the condition?
0|0|0|SCAN TABLE folder_id AS summary USING INDEX sqlite_autoindex_folder_id_1 0|1|1|SEARCH TABLE folder_id_email_list AS email_list USING INDEX UID_INDEX_email_folder_id (uid=?) 0|0|0|USE TEMP B-TREE FOR DISTINCT That still takes about a second and a half on my data set. Compared with 5-10ms for the UNION version: 1|0|1|SEARCH TABLE folder_id_email_list AS email_list USING INDEX INDEX_email_folder_id (value>? AND value<?) 1|1|0|SEARCH TABLE folder_id AS summary USING INDEX sqlite_autoindex_folder_id_1 (uid=3D?) 2|0|0|SEARCH TABLE folder_id AS summary USING INDEX INDEX_full_name_folder_id (full_name>? AND full_name<?) 0|0|0|COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION) It's better than the original left join version though; that was actually 1800ms (which is more than I said in my previous email.) -- dwmw2
_______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
