I'm new to SQLite, but have been working with SQL Server and mySQL for several years so I'm not entirely blind on this.
Trying to figure out how to cut down on the execution time of a query. Situation is I have a relatively simple table, with three fields: id (integer, primary key), hash (text), category (integer).
Problem is I have to put 6.5+ million records into it. I've created an Index on the category field, and there is a (mostly useless) index as part of the id field's primary key status. Running the following query, though, takes 3.5 minutes:
SELECT category, COUNT(category) AS total
FROM table_name
GROUP BY category
ORDER BY category
I'm using the System.Data.SQLite dll to work within a Visual Basic .NET Windows application. I've fiddled with increasing or decreasing the PRAGMA cache_size and setting temp_store to run in MEMORY, I've dropped and recreated the index after loading in the data (which also took forever), I've tried the VACUUM.
Is there something I'm missing? I haven't seen anything in my online searching to indicate that 6.5 million records is a problem - the limits I've seen are up in "pedibytes"...