Here is the complete schema: sqlite> .schema CREATE TABLE application (id INTEGER CONSTRAINT applpk PRIMARY KEY, shortname VARCHAR(64), name VARCHAR(256));
CREATE TABLE flows (idx INTEGER PRIMARY KEY, ipLan INTEGER, ipWan INTEGER, flags INTEGER, portLan INTEGER, portWan INTEGER, tos INTEGER, protocol INTEGER, wanTcpFlags INTEGER, nbBytesDecompOut INTEGER, nbBytesCompIn INTEGER, duration INTEGER, applId INTEGER, CST INTEGER, SRT INTEGER, RTT INTEGER, lan_retransmission INTEGER, wan_retransmission INTEGER, nbPktDecompIn INTEGER, nbPktCompOut INTEGER, nbBytesDecompIn INTEGER, nbBytesCompOut INTEGER, timestamp INTEGER, rtpTypeLAN INTEGER, rtpPktsLossLAN INTEGER, rtpJitterLAN INTEGER, rtpFactorRLAN INTEGER, rtpTypeWAN INTEGER, rtpPktsLossWAN INTEGER, rtpJitterWAN INTEGER, rtpFactorWLAN INTEGER, nbBytesDbcDecompOut INTEGER, nbBytesDbcCompIn INTEGER, nbBytesDefDecompOut INTEGER, nbBytesDefCompIn INTEGER, nbPktDecompOut INTEGER, nbPktCompIn INTEGER, nbBytesDbcDecompIn INTEGER, nbBytesDbcCompOut INTEGER, nbBytesDefDecompIn INTEGER, nbBytesDefCompOut INTEGER); CREATE INDEX idxApp ON flows(applId); 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); 2015-03-30 12:44 GMT+02:00 Jeff Roux <jfroux06 at gmail.com>: > 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 >> > >
