1

I'm creating a SQLite Database for Android. I can do fairly simple SQL, but I haven't used an index before. I'm working on a table with >150,000 records and I've never dealt with a database so large. I know that I need to add at least one index to make my queries faster. Here is the table:

CREATE TABLE IF NOT EXISTS T 
(
    a TEXT, b TEXT, c INT, d TEXT // ... 14 more TEXT columns
)

My only queries on this table are only on a, b, and c like this:

SELECT * FROM T WHERE c = ?
SELECT * FROM T WHERE a LIKE ?% AND b LIKE ?%
SELECT * FROM T WHERE a LIKE ?%
SELECT * FROM T WHERE b LIKE ?%

This table is filled once, and no changes are made to it after that. It's acceptable to take some extra time after import to add the index.

Can some one more knowledgeable than me tell me what index or indices I should create and if I should wait until after filling the table to add them?

This is my first question on this site. I thank you for you looking at this for me!

2
  • Why are you using LIKE instead of GLOB? Do you need case insensitivity?
    – CL.
    Apr 29 '14 at 9:37
  • Yes, I do need case insensitivity for those queries.
    – IowaEric
    Apr 30 '14 at 3:01
0
  • WHERE c = ?

    This lookup can be optimized with a simple index on the c column:

    CREATE INDEX T_c on T(c);
    
  • WHERE a LIKE ? || '%'

    There are several restrictions on indexes that can be used for LIKE searches. In this case, you need a case-insensitive index:

    CREATE INDEX T_a_ci ON T(a COLLATE NOCASE);
    
  • WHERE b LIKE ? || '%'

    same here:

    CREATE INDEX T_b_ci ON T(b COLLATE NOCASE);
    
  • WHERE a LIKE ? || '%' AND b LIKE ? || '%'

    The database can use at most one index per table in a query, so it will use one of the indexes above. If you have run ANALYZE, it might be able to choose the better one.

To check which indexes are actually being used, use EXPLAIN QUERY PLAN.

Whenever you change tables, any indexes must be updated too. When you're filling an empty table, it is usually a better idea to create indexes afterwards.

When your application does many updates to some table, it might be worthwhile omitting some index(es) that are useful only for seldomly-used queries.

1
  • YES! That took my queries from ~6 seconds to <200 ms! This is fantastic! I am EXTREMELY appreciative of your help. Thank you! Thank you! Thank you!
    – IowaEric
    May 1 '14 at 3:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.