Interesting. From that code you might want to try something like this:
SELECT uid, vcard, bdata
FROM folder_id
WHERE uid in ( select uid FROM email_list where value like 'p%'
union
select uid from folder_id where nickname LIKE 'p%'
union
select uid from folder_id where full_name LIKE 'p%'
union
select uid from folder_id where family_name LIKE 'p%'
union
select uid from folder_id where given_name LIKE 'p%'
union
select uid from folder_id where nickname LIKE 'p%'
union
select uid from folder_id where file_as LIKE 'p%'
);
Then having nocase indexes on the various search fields will all work as
expected.
>-----Original Message-----
>From: David Woodhouse [mailto:dw...@infradead.org]
>Sent: Wednesday, 24 September, 2014 07:25
>To: Keith Medcalf
>Cc: General Discussion of SQLite Database
>Subject: Re: [sqlite] 50% faster than 3.7.17
>
>On Wed, 2014-09-24 at 06:13 -0600, Keith Medcalf wrote:
>>
>> Would it not be more efficient to skip the join altogether since all
>> you want is the list of uid's, and assuming that you have maintained
>> the referential integrity of your database mail_list(list_uid)
>> references main(uid)?
>>
>> SELECT list_uid
>> FROM mail_list
>> WHERE email LIKE 'al%'
>> UNION
>> SELECT uid
>> FROM main
>> WHERE first_name LIKE 'al%'
>> OR last_name LIKE 'al%';
>
>Yes, but only because I oversimplified. In fact my queries are actually
>after *other* fields from the main table. It's just that I'd elided
>those fields from the example.
>
>For the sake of the example, please assume my queries were actually
> 'SELECT DISTINCT main.uid, main.first_name, main.last_name FROM …'
>
>The real-life query that this is simplified *from* is discussed at
>https://git.gnome.org/browse/evolution-data-server/commit/?id=5f9f5b52807
>
>--
>dwmw2
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
