On Thu, 2013-11-28 at 00:20 -0500, Igor Tandetnik wrote: > On 11/27/2013 11:52 PM, Tristan Van Berkom wrote: > > ================================================ > > SELECT DISTINCT summary.uid, summary.vcard FROM 'folder_id' AS summary > > LEFT OUTER JOIN 'folder_id_phone_list' AS phone_list > > ON phone_list.uid = summary.uid > > LEFT OUTER JOIN 'folder_id_email_list' AS email_list > > ON email_list.uid = summary.uid > > WHERE (phone_list.value IS NOT NULL AND phone_list.value LIKE '%0505') > > AND (email_list.value IS NOT NULL AND email_list.value LIKE 'eddie%') > > Why are you using outer joins when your WHERE clause discards unmatched > records anyway? If you replace LEFT OUTER with INNER, the end result > would be exactly the same. > > I have a strong feeling that, once you replace outer joins with regular > joins, this statement would run just as fast as your convoluted one with > nested selects. By using outer joins, you prevent SQLite from reordering > the conditions and using the most efficient search strategy - it is > forced to perform those joins left to right, which results in bad > performance because, apparently, you don't have indexes on > phone_list.uid or email_list.uid.
Actually... I just replied to this email but forgot to mention, while your input is really appreciated, it would be really great if you could answer the actual question :) I.e. is the statement logically the same ? > > Try this straightforward query: > > SELECT DISTINCT summary.uid, summary.vcard FROM folder_id AS summary > JOIN folder_id_phone_list AS phone_list > ON phone_list.uid = summary.uid > JOIN folder_id_email_list AS email_list > ON email_list.uid = summary.uid > WHERE phone_list.value LIKE '%0505' AND email_list.value LIKE 'eddie%'; > > > o Leverage the index which I've created on 'folder_id_email_list' > > (I am using case insensitive LIKE statements so the indexes > > work in that statement). > > Normally, you need case-sensitive LIKE in order to use the index, unless > the index is created with COLLATE NOCASE. You could use EXPLAIN QUERY > PLAN to confirm that the index is indeed being utilized. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
