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.
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.
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
