SQLite Select Query Performance Tuning

Question

I have the following query, which fetches data from only one table.

EDIT: It is a query which is supposed to return data for an autocomplete function.

1. Autocomplete data can be in text1 or text2.
2. The exact matches should be on top. int3 is an integer weight value, where the order of results are based on this. First two queries are here to identify exact matches.
3. The next two queries are here for the identify near matches. WHERE text1 > 'foo' AND text1 < 'fop' phrase is actually equal to WHERE text1 LIKE 'foo%'. I wrote it like this to benefit from index.

Hope this helps.

SELECT DISTINCT text1 as Key, 'text1' as Source, int1 as Count, 1000 as int3 
   FROM mytable 
   WHERE text1 = 'foo' 

UNION SELECT DISTINCT text2 as Key, 'text2' as Source, int2 as Count, 1000 as int3 
   FROM mytable 
   WHERE text2 = 'foo' 

UNION SELECT text1 as Key, 'text1' as Source, int1 as Count, MAX(int3) as int3 
   FROM mytable 
   WHERE text1 > 'foo' AND text1 < 'fop' AND Count < 4 
   GROUP BY Key 

UNION SELECT text2 as Key, 'text2' as Source, int2 as Count, MAX(int3) as int3 
   FROM mytable 
   WHERE text2 > 'foo' AND text2 < 'fop' AND Count < 4 
   GROUP BY Key 

ORDER BY int3 DESC, Count, Key LIMIT 0, 15;

The table structure is:

CREATE TABLE mytable (text1 TEXT, text2 TEXT, 
                      int1 NUMERIC, int2 NUMERIC, int3 NUMERIC);

It works fine, but I need to fine tune the performance. I tried different indexing options and checked the performance results with built in timer.

The best indexing options I discovered are :

CREATE INDEX cmp1 ON mytable (text1 ASC, int1 ASC, int3 DESC);
CREATE INDEX cmp2 ON mytable (text2 ASC, int2 ASC, int3 DESC);

I will be glad if you could show me any better indexing option or a better performing SQL query.

Answer 1

UNION removes duplicate rows that appear in both subqueries, and therefore must create a temporary table for the result.

If you can guarantee that the subqueries are distinct, or if you don't care about those duplicates, use UNION ALL instead.

(In your case, the sorting by the non-indexed value int3 requires a temporary table anyway.)

---

To optimize the subqueries, run them with EXPLAIN QUERY PLAN to check whether they use indexes.

You can use LIKE if you ensure that your indexes are declared correctly; see LIKE optimization.

See Query Planning for an explanation of how to construct indexes. Your two indexes seem to be optimal.