On 30 Mar 2015, at 10:46am, Jeff Roux <jfroux06 at gmail.com> wrote:
> 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; What indexes do you have on that table ? I would expect to get best results from one of the following indexes (portLan, protocol, timestamp) (portLan, timestamp) (protocol, portLan, timestamp) (protocol, timestamp) For best results, CREATE all of those indexes and any others that strike your fancy, run ANALYZE, then execute your SELECT. If this gives satisfactory results, use EXPLAIN QUERY PLAN [your SELECT statement] and see which index SQLite decided to use. You can then delete the other unused indexes and run ANALYZE one final time for a little more optimization. It's worth noting that SQLite, since it has no server, has no server-level caching. If you're doing repeated queries using mySQL, after the first one most of the index is cached so the times you're seeing are faster than SQLite can ever produce. Two disadvantages of mySQL are the memory usage and the work involved in administration. Simon.
