Sqlite query optimisation needed

Question

I'm using sqlite for a small validation application. I have a simple one table database with 4 varhchar columns and one integer primary key. There are close to 1 million rows in the table. I have optimised it and done a vacuum on it.

I am using the following query to retrieve a presence count from the table. I have changed the fields and names for privacy.

                  SELECT 
                  count(*) as 'test'
                  FROM
                  my_table
                  WHERE
                  LOWER(surname) = LOWER('Oliver')
                  AND
                  UPPER(address_line2) = UPPER('Somewhere over the rainbow')  
                  AND
                  house_number IN ('3','4','5');

This query takes about 1.5-1.9 seconds to run. I have tried indexes and they make no difference really. This time may not sound bad but I have to run this test about 40,000 times on a read in csv file so as you may imagine it adds up pretty quickly. Any ideas on how to reduce the execution time. I normally develop in mssql or mysql so if there are some tricks I am missing in sqlite I would be happy to hear them.

All the best.

Gerardo Lima · Accepted Answer · 2012-10-03T15:44:17.763

1

When you use a function over an indexed column, SQLite cannot use the index, because the function may not preserve the ordering -- i.e. there can be functions such as 1>2, but F(1)<F(2). There are some ways to solve this situation, though:

If you want to use indexes to make your query faster, you must save the value in a fixed case (upper or lower) and then convert only the query parameter to the same case:

SELECT count(*) as 'test'
FROM my_table
WHERE surname = LOWER('Oliver')

You can use the case-insensitive LIKE operator (I don't know how indexes are affected!):

SELECT count(*) as 'test'
FROM my_table
WHERE surname LIKE 'Oliver';

Or you can create each column as text collate nocase and don't worry about case differences regarding this column anymore:

CREATE TABLE my_table (surname text collate nocase, <... other fields here ...>);
SELECT count(*) as 'test'
FROM my_table
WHERE surname ='Oliver';

You can find more information about the = and LIKE operators here.

edited Oct 03 '12 at 15:44

answered Oct 03 '12 at 15:26

Gerardo Lima

6,467
3
31
47

You my dear man are a saviour, right to the heart of the problem in only a few seconds, fair play, my hat is off to you. I will mark this as answered with you answer in a few minutes when it lets me. If I could buy you a pint now I would. Thanks again. – jiraiya Oct 03 '12 at 15:30
Ps. The execution time is now 16ms...brilliant. – jiraiya Oct 03 '12 at 15:30
To see how you can make the search case insensitive, see this question: http://stackoverflow.com/questions/973541/how-to-set-sqlite3-to-be-case-insensitive-when-string-comparing. – Larry Lustig Oct 03 '12 at 15:32
Great information guys thanks a bunch. NOCASE all the way for me from now on. – jiraiya Oct 03 '12 at 15:46

score 1 · Answer 2 · answered Oct 03 '12 at 15:41

1

              SELECT  
              count(1) as 'test' 
              FROM 
              my_table 
              WHERE 
              surname = 'Oliver' 
              AND 
              address_line2 = 'Somewhere over the rainbow'   
              AND 
              house_number IN ('3','4','5')
              COLLATE NOCASE;

answered Oct 03 '12 at 15:41

Yaqub Ahmad

27,569
23
102
149

Very helpful to do this at the query level also thanks a million. – jiraiya Oct 03 '12 at 15:47

Sqlite query optimisation needed

2 Answers2