SQLite slowing down after millions of rows, how to speed up?

Question

I have a single table in SQLite that essentially has the following:

id : integer (primary key)
name: text
timestamp: text
data1: integer
data2: integer
...
data6: integer

I need to filter by name and timestamp, so I have the (name, timestamp) index set up.

At 100,000,000 rows, SQLite crawls at querying. Understand the index reduces time complexity from O(n) to O(log n) but it still seems to be too slow. I prefer not to slice the data into multiple tables. Any suggestions?

Answer 1

Your timestamp should be numeric. Filtering on a text column will significantly slow your queries because of the way strings are compared.

And if you've not already done so, put indexes on any column that is sorted (ORDER BY) or filtered (WHERE, HAVING,JOIN ON, etc.).

And finally, the order in which you filter your data can have a big difference. Filtering by numeric timestamp and then name will usually be significantly faster than filtering by name and then numeric timestamp. Try changing the order of your expressions. For example, WHERE day = ?, month = ?, year = ? will typically be much faster than WHERE year = ?, month = ?, day = ?.

Answer 2

Are you sure your query is using the composite index (try EXPLAIN QUERY PLAN ...)? After a certain number of inserts, it may no longer be unique enough. Try reversing the order of your composite index (timestamp,name instead of name,timestamp). Generally the columns should be ordered by selectivity.

Answer 3

For giggles, I created a sqlite3 database containing the schema of the OP containing 100'000'000 rows, 6GB unindexed database file using text datestamps.

With indexing, the database file doubled in size. With a pretty pedestrian desktop machine vintage 2008 (2GB RAM, 5k BogoMIPS) the query

select * from big where date = "2010-09-20 04:54:45.586836"; 

returned 10k rows in less than 8 seconds wall-clock time. I hope these numbers are useful for comparison.