SQLite Date index shows no performance improvement

Question

My SQLite database has over 500,000 rows. Being new I have just started using additional indexes in my VB.net application with some startling speed improvements :).

These improvements are not replicated when I index the Date column of my table. I am going to include some code snippets details below and would appreciate any input regarding what I may be doing wrong.

    'When creating the date column in DB (amongst others) 
oMainQueryR.CommandText += "RDate DATE, "

'creating index
oMainQueryR.CommandText = "Create INDEX main.iRDate ON DatabaseRows(RDate)"   
      oMainQueryR.ExecuteNonQuery()

'Storing the Data
.Parameters.Add(":Rdate", DbType.Date)
cmdSQLite.Parameters(":Rdate").Value = CDate(TextSplit(1)).ToString("dd/MM/yyyy")



'SQL Call to retrieve data
 oMainQueryR.CommandText = "SELECT * FROM DatabaseRows " _
                            & "WHERE DATE([RDate]) BETWEEN DATE(:StartDate) AND DATE(:EndDate) " _

The Calls and everything is working OK and I get the correct functionality its just there is no improvement when I index the RDate column speed wise.

I should mention that the index appears to be created correctly

Would appreciate any help, thank you

Answer 1

SQLite does not use an index for this lookup because you are not looking up a column value but the result of a function call, and it is not the value DATE(RDate) that is stored in the index.

Drop the DATE function from your query; it does nothing in your query except preventing optimizations:

... WHERE RDate BETWEEN :StartDate AND :EndDate

To check whether SQLite actually uses your index in a query, use EXPLAIN QUERY PLAN.

Please note dd/MM/yyyy is not one of SQLite's supported date formats; you must use yyyy-MM-dd for string comparisons to work correctly.

(For lookups like this, is does not matter much whether you store the date as a string or as a number.)

Answer 2

Consider storing your date data within INTEGER (Date data type is not intrinsically supported by SQLite).

That would turn query into

SELECT * FROM DatabaseRows WHERE RDate BETWEEN (:StartDate) AND (:EndDate);

which will avoid big amounts of DATE function calls.

Indeed, your DATE function call totally disables INDEX improvement.

Also, INTEGER comparison is way much faster then TEXT comparison.