CODEWITHSUNDEEP
sqliteindexing
Shaun
Shaun

Reputation: 2181

Do I need another index besides this one in SQLLite?

I have a unique index set up across three columns in SQLLite, and am wondering if I need any other indexes, as retrieving results from the following query sometimes takes almost 10 sec (database size is only about 25,000 rows):

SELECT * FROM books 
WHERE (Author="Roger Davies" COLLATE NOCASE AND 
Title>="My Best Days" COLLATE NOCASE AND 
CatID>"rtYY91" COLLATE NOCASE ) 

OR Author > "Roger Davies" COLLATE NOCASE  

ORDER BY Author COLLATE NOCASE ASC, 
Title COLLATE NOCASE ASC, 
CatID COLLATE NOCASE ASC LIMIT 10

The index is: CREATE UNIQUE INDEX booksIndex ON books ( CatID, Author, Title )

Thanks for your guidance.

Upvotes: 1

Views: 92

Answers (1)

CL.
CL.

Reputation: 180060

The index is not used at all. (You could have noticed this with EXPLAIN QUERY PLAN.)

To make it possible to use the index for this query, add COLLATE NOCASE to the columns in the index definition.

However, the columns in an index can be used only beginning with the leftmost index column, and an inequality comparison (like >=) can be used only in one column if an index, and prevents any further index columns from being used.

So the possible comparisons that could be optimized by this particular index are:

  • CatID = x; or
  • CatID > x; or
  • CatID = x AND Author = y; or
  • CatID = x AND Author > y; or
  • CatID = x AND Author = y AND Title = z; or
  • CatID = x AND Author = y AND Title > z.

(However, the optimizer is able to reorder the comparisons in the WHERE clause to match this.)

If you want to optimize a query that is sorted first by the author, you need an index with that as the first column.

If you can have different queries with different sort orders, you need multiple indexes. In that case, it might be a better idea to have only one-column indexes so that the number of indexes does not get out of hand.

Upvotes: 2

Related Questions