SQLite Select Query Performance Tuning

Question

I have the following query that retrieves data from only one table.

EDIT: This is a query that should return data for an autocomplete function.

• Autocomplete data can be in text1 or text2 .
• Exact matches must be on top. int3 is an integer weight value, where the order of the results is based on this. The first two queries are here for exact matches.
• The following two queries identify the closest matches. The phrase WHERE text1 > 'foo' AND text1 < 'fop' actually equal to WHERE text1 LIKE 'foo%' . I wrote this to benefit from the 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; 

Table structure:

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

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

The best indexing options I've discovered:

 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 can show me a better indexing option or a more efficient SQL query.

Answer 1

UNION removes duplicate rows that appear in both subqueries, and so you need to create a temporary table for the result.

If you can guarantee that the subqueries are different or you do not need these duplicates, use UNION ALL instead.

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

---

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

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

See Planning Queries for an explanation of how to create indexes. Your two indexes seem to be optimal.