On 2015-04-13 09:49 AM, Jeff Roux wrote:
> Hi everyone,
>
> I have copied the original database on my personnal website in a tbz
> archive here :
>
> http://nice-waterpolo.com/misc/db/
>
> There is only one index on timestamp,protocol.
Hi Jeff,
I am not sure what is wrong your side, but the table as I downloaded it
produced the said query (including all rows) in just over 2 seconds for
me - that's from a spinning disk.
Good news is I found some improvements. I've included the schema in the
script below to see I'm not cheating - I dropped the index and made only
1 index on timestamp - and then for the large query made it ignore the
index so it was faster through a table scan (but this changes when you
constrain the selection to a subset, as the last two queries in the
output show).
Your Query runs on the full set in sub 1-second times with this setup.
This ran in Windows in SQLitespeed while the GUI was up, so if I pushed
it through the sqlite3.exe command-line shell, another few milliseconds
would be shaved off.
After the output below I list the pragma settings too in case they
differ from your setup. I'm hoping the answer presents itself among all
this. (and I hope the e-mail word-wrapping doesn't mess it up too much).
-- Processing SQL in: D:\Documents\SQLiteAutoScript.sql
-- Script Items: 9 Parameter Count: 0
-- 2015-04-13 14:31:53.280 | [Info] Script Initialized,
Started executing...
--
================================================================================================
SELECT sqlite_version() AS Ver;
-- Ver
-- -------
-- 3.8.9
SELECT * FROM sqlite_master;
-- type | name | tbl_name | rootpage | sql
-- ------- | --------------------- | -------------- | ------------ |
------------------------------------------------------------
-- table | flows | flows | 2 | 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)
-- table | application | application | 4 |
CREATE TABLE application (id INTEGER CONSTRAINT applpk
-- | | | | PRIMARY KEY,
shortname VARCHAR(64), name VARCHAR(256))
-- table | sqlite_stat1 | sqlite_stat1 | 6 |
CREATE TABLE sqlite_stat1(tbl,idx,stat)
-- index | Idx_flows_timestamp | flows | 7 | CREATE
INDEX Idx_flows_timestamp ON flows (timestamp)
SELECT * FROM sqlite_stat1;
-- tbl | idx | stat
-- ------------- | --------------------- | ---------------
-- application | | 108
-- flows | Idx_flows_timestamp | 1000000 992
-- All-rows Query using the Index
EXPLAIN QUERY PLAN
SELECT SUM(nbBytesDecompOut+nbBytesCompIn) as vol, portLan as item
FROM flows
WHERE timestamp BETWEEN 1383770600 AND 1384770600 AND protocol IN (17, 6)
GROUP BY portLan
ORDER BY vol DESC LIMIT 6;
-- selectid | order | from | detail
-- -------- | ----- | ---- |
------------------------------------------------------------
-- 0 | 0 | 0 | SEARCH TABLE flows USING INDEX
Idx_flows_timestamp
-- | | | (timestamp>? AND timestamp<?)
-- 0 | 0 | 0 | USE TEMP B-TREE FOR GROUP BY
-- 0 | 0 | 0 | USE TEMP B-TREE FOR ORDER BY
SELECT SUM(nbBytesDecompOut+nbBytesCompIn) as vol, portLan as item
FROM flows
WHERE timestamp BETWEEN 1383770600 AND 1384770600 AND protocol IN (17, 6)
GROUP BY portLan
ORDER BY vol DESC LIMIT 6;
-- vol | item
-- ------------ | ------------
-- 2783898050 | 33722
-- 1374153827 | 33736
-- 886842830 | 39155
-- 655809252 | 51800
-- 363040479 | 53153
-- 358988337 | 59757
-- Item Stats: Item No: 5 Query Size
(Chars): 206
-- Result Columns: 2 Result Rows: 6
-- VM Work Steps: 22487216 Rows
Modified: 0
-- Full Query Time: 0d 00h 00m and 01.371s
-- Query Result: Success.
--
------------------------------------------------------------------------------------------------
-- All-rows Query preventing the Index to cause full table scan (faster
when the Query includes > ~90% of rows).
EXPLAIN QUERY PLAN
SELECT SUM(nbBytesDecompOut+nbBytesCompIn) as vol, portLan as item
FROM flows
WHERE +timestamp BETWEEN 1383770600 AND 1384770600 AND protocol IN (17, 6)
GROUP BY portLan
ORDER BY vol DESC LIMIT 6;
-- selectid | order | from | detail
-- -------- | ----- | ---- | ------------------------------
-- 0 | 0 | 0 | SCAN TABLE flows
-- 0 | 0 | 0 | USE TEMP B-TREE FOR GROUP BY
-- 0 | 0 | 0 | USE TEMP B-TREE FOR ORDER BY
SELECT SUM(nbBytesDecompOut+nbBytesCompIn) as vol, portLan as item
FROM flows
WHERE +timestamp BETWEEN 1383770600 AND 1384770600 AND protocol IN (17, 6)
GROUP BY portLan
ORDER BY vol DESC LIMIT 6;
-- vol | item
-- ------------ | ------------
-- 2783898050 | 33722
-- 1374153827 | 33736
-- 886842830 | 39155
-- 655809252 | 51800
-- 363040479 | 53153
-- 358988337 | 59757
-- Item Stats: Item No: 7 Query Size
(Chars): 207
-- Result Columns: 2 Result Rows: 6
-- VM Work Steps: 22487214 Rows
Modified: 0
-- Full Query Time: 0d 00h 00m and 00.954s
-- Query Result: Success.
--
------------------------------------------------------------------------------------------------
-- Smallish subset of rows using the Index (Faster).
SELECT SUM(nbBytesDecompOut+nbBytesCompIn) as vol, portLan as item
FROM flows
WHERE timestamp BETWEEN 1384749000 AND 1384770600 AND protocol IN (17, 6)
GROUP BY portLan
ORDER BY vol DESC LIMIT 6;
-- vol | item
-- ----------- | -------
-- 358983409 | 59757
-- 58342593 | 49245
-- 47269969 | 59607
-- 43424671 | 38931
-- 40937031 | 54032
-- 34347145 | 60048
-- Item Stats: Item No: 8 Query Size
(Chars): 260
-- Result Columns: 2 Result Rows: 6
-- VM Work Steps: 3918782 Rows
Modified: 0
-- Full Query Time: 0d 00h 00m and 00.275s
-- Query Result: Success.
--
------------------------------------------------------------------------------------------------
-- Smallish subset of rows through a table scan (Slower).
SELECT SUM(nbBytesDecompOut+nbBytesCompIn) as vol, portLan as item
FROM flows
WHERE +timestamp BETWEEN 1384749000 AND 1384770600 AND protocol IN (17, 6)
GROUP BY portLan
ORDER BY vol DESC LIMIT 6;
-- vol | item
-- ----------- | -------
-- 358983409 | 59757
-- 58342593 | 49245
-- 47269969 | 59607
-- 43424671 | 38931
-- 40937031 | 54032
-- 34347145 | 60048
-- Item Stats: Item No: 9 Query Size
(Chars): 266
-- Result Columns: 2 Result Rows: 6
-- VM Work Steps: 6455004 Rows
Modified: 0
-- Full Query Time: 0d 00h 00m and 00.510s
-- Query Result: Success.
--
------------------------------------------------------------------------------------------------
-- Script Stats: Total Script Execution Time: 0d 00h 00m and
03.144s
-- Total Script Query Time: 0d 00h 00m and
03.112s
-- Total Database Rows Changed: 0
-- Total Virtual-Machine Steps: 55348274
-- Last executed Item Index: 9
-- Last Script Error:
--
------------------------------------------------------------------------------------------------
-- 2015-04-13 14:31:56.405 | [Success] Script Success.
-- 2015-04-13 14:31:56.405 | [Success] Transaction Rolled back.
-- ------- DB-Engine Logs (Contains logged information from all DB
connections during run) ------
-- [2015-04-13 14:31:53.248] APPLICATION : Script
D:\Documents\SQLiteAutoScript.sql started at 14:31:53.248 on 13 April.
--
================================================================================================
Pragma Setting | Value
----------------------+------------------------------------------
application_id : 0
auto_vacuum : 0
automatic_index : 1
busy_timeout : 20
cache_size : 2000
compile_options :
ENABLE_COLUMN_METADATA,ENABLE_FTS3,ENABLE_RTREE,SYSTEM_MALLOC,THREADSAFE=1
collation_list : 0,1,2
defer_foreign_keys : 0
encoding : UTF-8
foreign_keys : 1
freelist_count : 0
incremental vacuum : 1024
journal_mode : truncate
journal_size_limit : -1
max_page_count : 1073741823
page_count : 139436
page_size : 1024
read_uncommitted : 0
recursive_triggers : 0
secure_delete : 0
user_version : 0
Best of luck,
Ryan
