Also tested with latest version 3.30.1. For showing problem I need just
2 tables (for join).
-- cca 30.000.000 recs
CREATE TABLE ARHDAJ(
ID_ARHDAJ integer primary key autoincrement,
ID_ARHPLA integer,
R integer,
O decimal(8,4),
Z decimal(12,2),
VEZA varchar(8) collate systemnocase);
CREATE UNIQUE INDEX ARHDAJ_IDX_ARHPLA on ARHDAJ (ID_ARHPLA,R,ID_ARHDAJ);
CREATE UNIQUE INDEX ARHDAJ_IDX_R on ARHDAJ (R,ID_ARHDAJ);
-- cca 100 recs
CREATE TABLE IZBPLA (
ID_PLA integer primary key autoincrement,
ID_DEL integer,
L4MM integer,
OE varchar(2) collate systemnocase,
SM varchar(4) collate systemnocase,
DN varchar(10) collate systemnocase,
DDM integer,
P integer,
U decimal(6,2),
UN decimal(6,2),
O decimal(8,4),
ZO decimal(12,2),
ZB decimal(12,2),
ZN decimal(12,2),
OS decimal(8,4),
DOH varchar(1) collate systemnocase);
CREATE UNIQUE INDEX IZBPLA_IDX_DEL on IZBPLA(ID_DEL, ID_PLA);
CREATE UNIQUE INDEX IZBPLA_IDX_P on IZBPLA(P, ID_PLA);
Problematic sql is:
select
R.ID_ARHDAJ, R.ID_ARHPLA, R.R, R.O, R.Z, R.VEZA
from IZBPLA P
left join ARHDAJ R on R.ID_ARHPLA = P.ID_PLA
where P.ID_PLA < 1000000000
and R.ID_ARHDAJ is not null;
explain query plan before 3.28.0 is:
0: SEARCH TABLE IZBPLA AS P USING INTEGER PRIMARY KEY (rowid<?)
1: SEARCH TABLE ARHDAJ AS R USING INDEX ARHDAJ_IDX_ARHPLA (ID_ARHPLA=?)
explain query plan after 3.28.0 is:
0: SEARCH TABLE ARHDAJ AS R USING INDEX ARHDAJ_IDX_ARHPLA (ID_ARHPLA=?)
1: SEARCH TABLE IZBPLA AS P USING INTEGER PRIMARY KEY (rowid<?)
Workaround is to change "and R.ID_ARHDAJ is not null" to "and
ifnull(R.ID_ARHDAJ,0) <> 0".
So sqlite goes through large table but should go through small table and
search record in
large for every record.
Best Regards
Radovan Antloga
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
