How to optimize delete with date range where clause?

Question

I created an android app and there is a function that will periodically delete old records

delete from tablea where col1 = 'value1' and col2 = 'value2' and postdate < '2010-06-14'

It has performance problem when total number of row in tablea has more then 50,000. It take around 45 seconds to delete 500 records.

I already have index for this where clause:

CREATE INDEX indexa on tablea (col1, col2, postdate)

adding PRAGMA synchronous=OFF and PRAGMA count_changes=OFF didn't help.

Please advise

Answer 1

It looks to me as though your index isn't useful as written. I think you'd want:

CREATE INDEX indexa on tablea (col1, col2)
CREATE INDEX indexb on tablea (postdate)

As read, my interpretation of the documentation says that to figure out what row is being referred to in your example, the database needs to have a specific date to match with a given pair of values (so it can look up the index/indexes). You don't have a specific date. Instead you're looking for a range of values of date.

The problem is the comparability of the date values is subsumed in the combination index you created, so you can't get that information out very easily, which makes the index of limited use.

Of course I could be full of it. My primary experience isn't with sqlite. Still, it shouldn't hurt much to try the above.

Answer 2

Check the type of your postdate field. SELECT typeof(postdate). It looks like SQLite is going to treat it as TEXT based on your where clause. SQLite has no concept of a 'date' type only a NUMERIC affinity is going to happen for any 'dates'. If you're not inserting NUMERICs then it's probably doing string comparisons and that is going to be slower than the integer comparisons that you are expecting. If you are inserting NUMERICs then your where clause may be causing them to be cast into TEXT first and then the where clause criteria being applied.

You can check out the documentation about datatypes.