This is the query that gets made when I just start typing into an Evolution composer window. It really ought to be one that we optimise for, right?

 "(or (beginswith "nickname"  "dave") (beginswith "email"  "dave") (beginswith "full_name"  "dave") (beginswith "file_as"  "dave") )"


STMT: EXPLAIN QUERY PLAN SELECT DISTINCT summary.uid, summary.vcard, summary.bdata FROM 'folder_id' AS summary JOIN 'folder_id_email_list' AS email_list ON +email_list.uid = summary.uid WHERE ((summary.nickname IS NOT NULL AND summary.nickname LIKE 'dave%') OR (email_list.value IS NOT NULL AND email_list.value LIKE 'dave%') OR ((summary.full_name IS NOT NULL AND summary.full_name LIKE 'dave%') OR (summary.family_name IS NOT NULL AND summary.family_name LIKE 'dave%') OR (summary.given_name IS NOT NULL AND summary.given_name LIKE 'dave%') OR (summary.nickname IS NOT NULL AND summary.nickname LIKE 'dave%')) OR (summary.file_as IS NOT NULL AND summary.file_as LIKE 'dave%'))
  PLAN: SCAN TABLE folder_id_email_list AS email_list
  PLAN: SEARCH TABLE folder_id AS summary USING INDEX sqlite_autoindex_folder_id_1 (uid=?)
  PLAN: USE TEMP B-TREE FOR DISTINCT
EXPLAIN END
TIME: 1361 ms

That sucks. Firstly, we don't have indices on many of those fields by default, despite the fact that we expect them to get used a *lot*. We do have an index on file_as_localized, for example, but not on file_as.

But even if we have those indices, they don't get *used* for this particular query. Probably because of the JOIN.

The JOIN is also wrong, AFAICT. It's an inner join (by default) and we actually want an OUTER join. An inner join will omit records which don't have *any* email addresses, so even if they match the file_as or nickname or other search criteria, they won't get found.

I think the query we actually want is more like...

 SELECT DISTINCT summary.uid, summary.vcard, summary.bdata FROM 'folder_id' AS summary JOIN 'folder_id_email_list' AS email_list ON +email_list.uid = summary.uid WHERE (email_list.value IS NOT NULL AND email_list.value LIKE 'dave%') union select  summary.uid,summary.vcard,summary.bdata from folder_id as summary where  ((summary.full_name IS NOT NULL AND summary.full_name LIKE 'dave%') OR (summary.family_name IS NOT NULL AND summary.family_name LIKE 'dave%') OR (summary.given_name IS NOT NULL AND summary.given_name LIKE 'dave%') OR (summary.nickname IS NOT NULL AND summary.nickname LIKE 'dave%')) OR (summary.file_as IS NOT NULL AND summary.file_as LIKE 'dave%') OR (summary.nickname IS NOT NULL AND summary.nickname LIKE 'dave%') ;

That does the email check, and the check on the other fields, separately. So we don't need to worry about needing an OUTER JOIN, and the check on the other fields can use the indices on them (which I've created manually, since most were missing).

That runs in about 520ms instead of 1400. If I drop all those IS NOT NULL checks it goes down to about 450ms.

1|0|1|SCAN TABLE folder_id_email_list AS email_list
1|1|0|SEARCH TABLE folder_id AS summary USING INDEX sqlite_autoindex_folder_id_1 (uid=?)
2|0|0|SCAN TABLE folder_id AS summary
0|0|0|COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)