Hi,
I am the main developer of Skrooge (application using sqlite) and since
my upgrade to ubuntu 13.04, I have very bad performances due to sqlite.
Here is my analysis:
All tests are done with the file
http://skrooge.org/files/skrooge_bug_performance.sqlite
*=====================================================================*
*ON UBUNTU QUANTAL:sqlite version 3.7.13 2012-06-11 02:05:22
f5b5a13f7394dc143aa136f1d4faba6839eaa6dc*
sqlite> SELECT
ABS(TOTAL(f_REALCURRENTAMOUNT))>=400,ABS(TOTAL(f_REALCURRENTAMOUNT))
FROM v_operation_consolidated WHERE d_date!='0000-00-00' AND
((STRFTIME('%Y-%m',d_date)=STRFTIME('%Y-%m',date('now','start of
month','-1 month'))));
1|2704.0
CPU Time: user *0.180000* sys 0.012000 (*GOOD PERFORMANCES*)
sqlite> SELECT * FROM v_category_display;
...
CPU Time: user 0.812000 sys 0.016000
sqlite> explain query plan SELECT * FROM v_category_display;
sele order from deta
---- ------------- ---- ----
1 0 4 SCAN TABLE suboperation USING INDEX
idx_suboperation_category_id (~7341 rows)
1 1 0 SEARCH TABLE operation USING INTEGER PRIMARY
KEY (rowid=?) (~1 rows)
1 2 1 SEARCH TABLE account USING INTEGER PRIMARY
KEY (rowid=?) (~1 rows)
1 3 2 SEARCH TABLE bank USING INTEGER PRIMARY KEY
(rowid=?) (~1 rows)
1 4 3 SEARCH TABLE unit USING INTEGER PRIMARY KEY
(rowid=?) (~1 rows)
1 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 2
2 0 0 SEARCH TABLE unit USING INTEGER PRIMARY KEY
(rowid=?) (~1 rows)
2 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 3
3 0 0 SEARCH TABLE unitvalue AS s USING INDEX
uidx_unitvalue2 (rd_unit_id=? AND d_date=?) (~1 rows)
3 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 4
4 0 0 SEARCH TABLE unitvalue AS s USING COVERING
INDEX uidx_unitvalue2 (rd_unit_id=?) (~1 rows)
2 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 5
5 0 0 SEARCH TABLE unit USING INTEGER PRIMARY KEY
(rowid=?) (~1 rows)
5 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 6
6 0 0 SEARCH TABLE unitvalue AS s USING INDEX
uidx_unitvalue2 (rd_unit_id=? AND d_date=?) (~1 rows)
6 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 7
7 0 0 SEARCH TABLE unitvalue AS s USING COVERING
INDEX uidx_unitvalue2 (rd_unit_id=?) (~1 rows)
0 0 0 SCAN TABLE category (~78 rows)
0 1 1 SEARCH SUBQUERY 1 AS t USING AUTOMATIC
COVERING INDEX (i_IDCATEGORY=?) (~3 rows)
0 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 8
8 0 1 SEARCH TABLE suboperation AS so USING INDEX
idx_suboperation_category_id (r_category_id=?) (~102 rows)
8 1 0 SEARCH TABLE operation AS o USING INTEGER
PRIMARY KEY (rowid=?) (~1 rows)
0 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 9
10 0 4 SCAN TABLE suboperation USING INDEX
idx_suboperation_category_id (~7341 rows)
10 1 0 SEARCH TABLE operation USING INTEGER PRIMARY
KEY (rowid=?) (~1 rows)
10 2 1 SEARCH TABLE account USING INTEGER PRIMARY
KEY (rowid=?) (~1 rows)
10 3 2 SEARCH TABLE bank USING INTEGER PRIMARY KEY
(rowid=?) (~1 rows)
10 4 3 SEARCH TABLE unit USING INTEGER PRIMARY KEY
(rowid=?) (~1 rows)
10 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 11
11 0 0 SEARCH TABLE unit USING INTEGER PRIMARY KEY
(rowid=?) (~1 rows)
11 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 12
12 0 0 SEARCH TABLE unitvalue AS s USING INDEX
uidx_unitvalue2 (rd_unit_id=? AND d_date=?) (~1 rows)
12 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 13
13 0 0 SEARCH TABLE unitvalue AS s USING COVERING
INDEX uidx_unitvalue2 (rd_unit_id=?) (~1 rows)
11 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 14
14 0 0 SEARCH TABLE unit USING INTEGER PRIMARY KEY
(rowid=?) (~1 rows)
14 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 15
15 0 0 SEARCH TABLE unitvalue AS s USING INDEX
uidx_unitvalue2 (rd_unit_id=? AND d_date=?) (~1 rows)
15 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 16
16 0 0 SEARCH TABLE unitvalue AS s USING COVERING
INDEX uidx_unitvalue2 (rd_unit_id=?) (~1 rows)
9 0 0 SCAN TABLE category (~39 rows)
9 1 1 SEARCH SUBQUERY 10 AS t USING AUTOMATIC
COVERING INDEX (i_IDCATEGORY=?) (~3 rows)
0 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 17
17 0 1 SEARCH TABLE suboperation AS so USING INDEX
idx_suboperation_category_id (r_category_id=?) (~102 rows)
17 1 0 SEARCH TABLE operation AS o USING INTEGER
PRIMARY KEY (rowid=?) (~1 rows)
0 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 18
19 0 4 SCAN TABLE suboperation USING INDEX
idx_suboperation_category_id (~7341 rows)
19 1 0 SEARCH TABLE operation USING INTEGER PRIMARY
KEY (rowid=?) (~1 rows)
19 2 1 SEARCH TABLE account USING INTEGER PRIMARY
KEY (rowid=?) (~1 rows)
19 3 2 SEARCH TABLE bank USING INTEGER PRIMARY KEY
(rowid=?) (~1 rows)
19 4 3 SEARCH TABLE unit USING INTEGER PRIMARY KEY
(rowid=?) (~1 rows)
19 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 20
20 0 0 SEARCH TABLE unit USING INTEGER PRIMARY KEY
(rowid=?) (~1 rows)
20 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 21
21 0 0 SEARCH TABLE unitvalue AS s USING INDEX
uidx_unitvalue2 (rd_unit_id=? AND d_date=?) (~1 rows)
21 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 22
22 0 0 SEARCH TABLE unitvalue AS s USING COVERING
INDEX uidx_unitvalue2 (rd_unit_id=?) (~1 rows)
20 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 23
23 0 0 SEARCH TABLE unit USING INTEGER PRIMARY KEY
(rowid=?) (~1 rows)
23 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 24
24 0 0 SEARCH TABLE unitvalue AS s USING INDEX
uidx_unitvalue2 (rd_unit_id=? AND d_date=?) (~1 rows)
24 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 25
25 0 0 SEARCH TABLE unitvalue AS s USING COVERING
INDEX uidx_unitvalue2 (rd_unit_id=?) (~1 rows)
18 0 0 SCAN TABLE category (~39 rows)
18 1 1 SEARCH SUBQUERY 19 AS t USING AUTOMATIC
COVERING INDEX (i_IDCATEGORY=?) (~3 rows)
18 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 26
26 0 1 SEARCH TABLE suboperation AS so USING INDEX
idx_suboperation_category_id (r_category_id=?) (~102 rows)
26 1 0 SEARCH TABLE operation AS o USING INTEGER
PRIMARY KEY (rowid=?) (~1 rows)
0 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 27
27 0 0 SEARCH TABLE category AS c USING AUTOMATIC
COVERING INDEX (t_bookmarked=?) (~3 rows)
*=====================================================================*
*ON UBUNTU RARING:sqlite version 3.7.15.2 2013-01-09 11:53:05
c0e09560d26f0a6456be9dd3447f5311eb4f238f*
sqlite> SELECT
ABS(TOTAL(f_REALCURRENTAMOUNT))>=400,ABS(TOTAL(f_REALCURRENTAMOUNT))
FROM v_operation_consolidated WHERE d_date!='0000-00-00' AND
((STRFTIME('%Y-%m',d_date)=STRFTIME('%Y-%m',date('now','start of
month','-1 month'))));
1|2704.0
CPU Time: user *1.072000* sys 0.000000 (*BAD PERFORMANCE*)
sqlite> SELECT * FROM v_category_display;
...
CPU Time: user 1.076000 sys 0.016000
sqlite> explain query plan SELECT * FROM v_category_display;
sele order from deta
---- ------------- ---- ----
1 0 2 SCAN TABLE bank USING COVERING INDEX
uidx_bank_name (~7 rows)
1 1 1 SEARCH TABLE account USING INDEX
idx_account_bank_id (rd_bank_id=?) (~4 rows)
1 2 3 SCAN TABLE unit (~19 rows)
1 3 0 SEARCH TABLE operation USING INDEX
idx_operation_rd_account_id_i_number (rd_account_id=?) (~3 rows)
1 4 4 SEARCH TABLE suboperation USING INDEX
idx_suboperation_operation_id (rd_operation_id=?) (~2 rows)
1 0 0 USE TEMP B-TREE FOR GROUP BY
1 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 2
2 0 0 SEARCH TABLE unit USING INTEGER PRIMARY KEY
(rowid=?) (~1 rows)
2 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 3
3 0 0 SEARCH TABLE unitvalue AS s USING INDEX
uidx_unitvalue2 (rd_unit_id=? AND d_date=?) (~1 rows)
3 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 4
4 0 0 SEARCH TABLE unitvalue AS s USING COVERING
INDEX uidx_unitvalue2 (rd_unit_id=?) (~1 rows)
2 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 5
5 0 0 SEARCH TABLE unit USING INTEGER PRIMARY KEY
(rowid=?) (~1 rows)
5 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 6
6 0 0 SEARCH TABLE unitvalue AS s USING INDEX
uidx_unitvalue2 (rd_unit_id=? AND d_date=?) (~1 rows)
6 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 7
7 0 0 SEARCH TABLE unitvalue AS s USING COVERING
INDEX uidx_unitvalue2 (rd_unit_id=?) (~1 rows)
0 0 0 SCAN TABLE category (~78 rows)
0 1 1 SEARCH SUBQUERY 1 AS t USING AUTOMATIC
COVERING INDEX (i_IDCATEGORY=?) (~3 rows)
0 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 8
8 0 1 SEARCH TABLE suboperation AS so USING INDEX
idx_suboperation_category_id (r_category_id=?) (~102 rows)
8 1 0 SEARCH TABLE operation AS o USING INTEGER
PRIMARY KEY (rowid=?) (~1 rows)
0 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 9
10 0 2 SCAN TABLE bank USING COVERING INDEX
uidx_bank_name (~7 rows)
10 1 1 SEARCH TABLE account USING INDEX
idx_account_bank_id (rd_bank_id=?) (~4 rows)
10 2 3 SCAN TABLE unit (~19 rows)
10 3 0 SEARCH TABLE operation USING INDEX
idx_operation_rd_account_id_i_number (rd_account_id=?) (~3 rows)
10 4 4 SEARCH TABLE suboperation USING INDEX
idx_suboperation_operation_id (rd_operation_id=?) (~2 rows)
10 0 0 *USE TEMP B-TREE FOR GROUP BY*
10 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 11
11 0 0 SEARCH TABLE unit USING INTEGER PRIMARY KEY
(rowid=?) (~1 rows)
11 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 12
12 0 0 SEARCH TABLE unitvalue AS s USING INDEX
uidx_unitvalue2 (rd_unit_id=? AND d_date=?) (~1 rows)
12 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 13
13 0 0 SEARCH TABLE unitvalue AS s USING COVERING
INDEX uidx_unitvalue2 (rd_unit_id=?) (~1 rows)
11 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 14
14 0 0 SEARCH TABLE unit USING INTEGER PRIMARY KEY
(rowid=?) (~1 rows)
14 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 15
15 0 0 SEARCH TABLE unitvalue AS s USING INDEX
uidx_unitvalue2 (rd_unit_id=? AND d_date=?) (~1 rows)
15 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 16
16 0 0 SEARCH TABLE unitvalue AS s USING COVERING
INDEX uidx_unitvalue2 (rd_unit_id=?) (~1 rows)
9 0 0 SCAN TABLE category (~39 rows)
9 1 1 SEARCH SUBQUERY 10 AS t USING AUTOMATIC
COVERING INDEX (i_IDCATEGORY=?) (~3 rows)
0 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 17
17 0 1 SEARCH TABLE suboperation AS so USING INDEX
idx_suboperation_category_id (r_category_id=?) (~102 rows)
17 1 0 SEARCH TABLE operation AS o USING INTEGER
PRIMARY KEY (rowid=?) (~1 rows)
0 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 18
19 0 2 SCAN TABLE bank USING COVERING INDEX
uidx_bank_name (~7 rows)
19 1 1 SEARCH TABLE account USING INDEX
idx_account_bank_id (rd_bank_id=?) (~4 rows)
19 2 3 SCAN TABLE unit (~19 rows)
19 3 0 SEARCH TABLE operation USING INDEX
idx_operation_rd_account_id_i_number (rd_account_id=?) (~3 rows)
19 4 4 SEARCH TABLE suboperation USING INDEX
idx_suboperation_operation_id (rd_operation_id=?) (~2 rows)
19 0 0 *USE TEMP B-TREE FOR GROUP BY*
19 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 20
20 0 0 SEARCH TABLE unit USING INTEGER PRIMARY KEY
(rowid=?) (~1 rows)
20 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 21
21 0 0 SEARCH TABLE unitvalue AS s USING INDEX
uidx_unitvalue2 (rd_unit_id=? AND d_date=?) (~1 rows)
21 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 22
22 0 0 SEARCH TABLE unitvalue AS s USING COVERING
INDEX uidx_unitvalue2 (rd_unit_id=?) (~1 rows)
20 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 23
23 0 0 SEARCH TABLE unit USING INTEGER PRIMARY KEY
(rowid=?) (~1 rows)
23 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 24
24 0 0 SEARCH TABLE unitvalue AS s USING INDEX
uidx_unitvalue2 (rd_unit_id=? AND d_date=?) (~1 rows)
24 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 25
25 0 0 SEARCH TABLE unitvalue AS s USING COVERING
INDEX uidx_unitvalue2 (rd_unit_id=?) (~1 rows)
18 0 0 SCAN TABLE category (~39 rows)
18 1 1 SEARCH SUBQUERY 19 AS t USING AUTOMATIC
COVERING INDEX (i_IDCATEGORY=?) (~3 rows)
18 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 26
26 0 1 SEARCH TABLE suboperation AS so USING INDEX
idx_suboperation_category_id (r_category_id=?) (~102 rows)
26 1 0 SEARCH TABLE operation AS o USING INTEGER
PRIMARY KEY (rowid=?) (~1 rows)
0 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 27
27 0 0 SEARCH TABLE category AS c USING AUTOMATIC
COVERING INDEX (t_bookmarked=?) (~3 rows)
*=====================================================================*
*WITH LAST VERSION:sqlite version 3.7.16.2 2013-04-12 11:52:43
cbea02d93865ce0e06789db95fd9168ebac970c7*
sqlite> SELECT
ABS(TOTAL(f_REALCURRENTAMOUNT))>=400,ABS(TOTAL(f_REALCURRENTAMOUNT))
FROM v_operation_consolidated WHERE d_date!='0000-00-00' AND
((STRFTIME('%Y-%m',d_date)=STRFTIME('%Y-%m',date('now','start of
month','-1 month'))));
1|2704.0
CPU Time: user *1.200000* sys 0.012000 (*BAD PERFORMANCE*)
sqlite> SELECT * FROM v_category_display;
CPU Time: user 1.384000 sys 0.016000
sqlite> explain query plan SELECT * FROM v_category_display;
1|0|2|SCAN TABLE bank USING COVERING INDEX uidx_bank_name (~7 rows)
1|1|1|SEARCH TABLE account USING INDEX idx_account_bank_id
(rd_bank_id=?) (~4 rows)
1|2|3|SCAN TABLE unit (~19 rows)
1|3|0|SEARCH TABLE operation USING INDEX
idx_operation_rd_account_id_i_number (rd_account_id=?) (~3 rows)
1|4|4|SEARCH TABLE suboperation USING INDEX
idx_suboperation_operation_id (rd_operation_id=?) (~2 rows)
1|0|0|USE TEMP B-TREE FOR GROUP BY
1|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2
2|0|0|SEARCH TABLE unit USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
2|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 3
3|0|0|SEARCH TABLE unitvalue AS s USING INDEX uidx_unitvalue2
(rd_unit_id=? AND d_date=?) (~1 rows)
3|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 4
4|0|0|SEARCH TABLE unitvalue AS s USING COVERING INDEX uidx_unitvalue2
(rd_unit_id=?) (~1 rows)
2|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 5
5|0|0|SEARCH TABLE unit USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
5|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 6
6|0|0|SEARCH TABLE unitvalue AS s USING INDEX uidx_unitvalue2
(rd_unit_id=? AND d_date=?) (~1 rows)
6|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 7
7|0|0|SEARCH TABLE unitvalue AS s USING COVERING INDEX uidx_unitvalue2
(rd_unit_id=?) (~1 rows)
0|0|0|SCAN TABLE category (~78 rows)
0|1|1|SEARCH SUBQUERY 1 AS t USING AUTOMATIC COVERING INDEX
(i_IDCATEGORY=?) (~3 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 8
8|0|1|SEARCH TABLE suboperation AS so USING INDEX
idx_suboperation_category_id (r_category_id=?) (~102 rows)
8|1|0|SEARCH TABLE operation AS o USING INTEGER PRIMARY KEY (rowid=?)
(~1 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 9
10|0|2|SCAN TABLE bank USING COVERING INDEX uidx_bank_name (~7 rows)
10|1|1|SEARCH TABLE account USING INDEX idx_account_bank_id
(rd_bank_id=?) (~4 rows)
10|2|3|SCAN TABLE unit (~19 rows)
10|3|0|SEARCH TABLE operation USING INDEX
idx_operation_rd_account_id_i_number (rd_account_id=?) (~3 rows)
10|4|4|SEARCH TABLE suboperation USING INDEX
idx_suboperation_operation_id (rd_operation_id=?) (~2 rows)
10|0|0|*USE TEMP B-TREE FOR GROUP BY*
10|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 11
11|0|0|SEARCH TABLE unit USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
11|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 12
12|0|0|SEARCH TABLE unitvalue AS s USING INDEX uidx_unitvalue2
(rd_unit_id=? AND d_date=?) (~1 rows)
12|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 13
13|0|0|SEARCH TABLE unitvalue AS s USING COVERING INDEX uidx_unitvalue2
(rd_unit_id=?) (~1 rows)
11|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 14
14|0|0|SEARCH TABLE unit USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
14|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 15
15|0|0|SEARCH TABLE unitvalue AS s USING INDEX uidx_unitvalue2
(rd_unit_id=? AND d_date=?) (~1 rows)
15|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 16
16|0|0|SEARCH TABLE unitvalue AS s USING COVERING INDEX uidx_unitvalue2
(rd_unit_id=?) (~1 rows)
9|0|0|SCAN TABLE category (~39 rows)
9|1|1|SEARCH SUBQUERY 10 AS t USING AUTOMATIC COVERING INDEX
(i_IDCATEGORY=?) (~3 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 17
17|0|1|SEARCH TABLE suboperation AS so USING INDEX
idx_suboperation_category_id (r_category_id=?) (~102 rows)
17|1|0|SEARCH TABLE operation AS o USING INTEGER PRIMARY KEY (rowid=?)
(~1 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 18
19|0|2|SCAN TABLE bank USING COVERING INDEX uidx_bank_name (~7 rows)
19|1|1|SEARCH TABLE account USING INDEX idx_account_bank_id
(rd_bank_id=?) (~4 rows)
19|2|3|SCAN TABLE unit (~19 rows)
19|3|0|SEARCH TABLE operation USING INDEX
idx_operation_rd_account_id_i_number (rd_account_id=?) (~3 rows)
19|4|4|SEARCH TABLE suboperation USING INDEX
idx_suboperation_operation_id (rd_operation_id=?) (~2 rows)
19|0|0|*USE TEMP B-TREE FOR GROUP BY*
19|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 20
20|0|0|SEARCH TABLE unit USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
20|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 21
21|0|0|SEARCH TABLE unitvalue AS s USING INDEX uidx_unitvalue2
(rd_unit_id=? AND d_date=?) (~1 rows)
21|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 22
22|0|0|SEARCH TABLE unitvalue AS s USING COVERING INDEX uidx_unitvalue2
(rd_unit_id=?) (~1 rows)
20|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 23
23|0|0|SEARCH TABLE unit USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
23|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 24
24|0|0|SEARCH TABLE unitvalue AS s USING INDEX uidx_unitvalue2
(rd_unit_id=? AND d_date=?) (~1 rows)
24|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 25
25|0|0|SEARCH TABLE unitvalue AS s USING COVERING INDEX uidx_unitvalue2
(rd_unit_id=?) (~1 rows)
18|0|0|SCAN TABLE category (~39 rows)
18|1|1|SEARCH SUBQUERY 19 AS t USING AUTOMATIC COVERING INDEX
(i_IDCATEGORY=?) (~3 rows)
18|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 26
26|0|1|SEARCH TABLE suboperation AS so USING INDEX
idx_suboperation_category_id (r_category_id=?) (~102 rows)
26|1|0|SEARCH TABLE operation AS o USING INTEGER PRIMARY KEY (rowid=?)
(~1 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 27
27|0|0|SEARCH TABLE category AS c USING AUTOMATIC COVERING INDEX
(t_bookmarked=?) (~3 rows)
CPU Time: user 0.088000 sys 0.000000
*=====================================================================*
*Conclusion:*
The same sql order is *7 times slower* on last version of sqlite than on
3.7.13 (0.18 to 1.2).
The regression seems to be introduced by 3.7.15.
The query plan is totaly different:
-In 3.7.13, no "USE TEMP B-TREE".
-After "USE TEMP B-TREE"
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
