On 2015-04-01 10:20 AM, Jeff Roux wrote: > 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);
You may remove all those Indices (unless you need them for something else) and jfor the specific query ust use: CREATE INDEX tsIdxProt ON flows(timestamp,protocol); Which, as done in the test scripts for 3 million rows I've sent earlier, will produce very fast results for your query (Sub 50 milliseconds for 12K rows). The actual time will greatly depend on the amount of hits qualified by the WHERE clause, but the times will be comparable to MySQL or any other SQL service (uncached).
