Simon, here is the list of the indexes that were already defined on the table: CREATE INDEX idxLanWan ON flows(ipLan, ipWan, portWan, portLan); CREATE INDEX idxProto ON flows(protocol); CREATE INDEX idxTos ON flows(tos); CREATE INDEX idxTsLanWan ON flows(timestamp, ipLan, ipWan, portWan, portLan); CREATE INDEX tsindex ON flows(timestamp);
I added the ones you gave me: CREATE INDEX ts1 ON flows (portLan, protocol, timestamp); CREATE INDEX ts2 ON flows (portLan, timestamp); CREATE INDEX ts3 ON flows (protocol, portLan, timestamp); CREATE INDEX ts4 ON flows (protocol, timestamp); and it appears it now uses the ts4 index, but the time spent is still ~ 5s sqlite> EXPLAIN QUERY PLAN 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; 0|0|0|SEARCH TABLE flows USING INDEX ts4 (protocol=? AND timestamp>? AND timestamp<?) (~31250 rows) 0|0|0|EXECUTE LIST SUBQUERY 1 0|0|0|USE TEMP B-TREE FOR GROUP BY 0|0|0|USE TEMP B-TREE FOR ORDER BY 2015-03-30 12:20 GMT+02:00 Simon Slavin <slavins at bigfraud.org>: > > 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. > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
