Hi everyone, I have a daemon that collects information and stores it in a SQLite database. The table has 1 million rows.
This daemon is running on a HP server with 12 cores, 32 GB of RAM, and a SSD drive. I have performance issues with some requests. For instance, the following request takes more than 5 seconds to accomplish with SQlite3 (in that particular case, the WHERE clause selects all the data in the database, i.e. 1000000 rows): SELECT SUM(nbBytesDecompOut + nbBytesCompIn) as vol, portLan as item FROM flows WHERE timestamp>=1383770600 AND timestamp<=1384770600 AND protocol IN (17, 6) GROUP BY portLan ORDER BY vol DESC LIMIT 6; I have done some tests with or without "INDEXED BY" clauses and got nearly the same results. I compared the performance with a mySQL and the same request takes less than 1 second to accomplish. Could you give me some directions to optimize this kind of request with SQlite3 when there is a big amount of data in the table ? I need to increase 3 times the number of rows and the performance will become unacceptable for my application. Thanks in advance.
