I have the following query, which fetches data from only one table.
It is a query which is supposed to return data for an autocomplete function.
- Autocomplete data can be in
- The exact matches should be on top.
int3is an integer weight value, where the order of results are based on this. First two queries are here to identify exact matches.
- 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 the 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.