Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

  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 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.

share|improve this question
    
duplicate of SQLite Select Query Performance Tuning on SO –  CL. Sep 27 '12 at 8:35
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.