Hi, if I do an ANALYZE then works, bat because it changes if I use left instead of inner
this is the result of .fullschema CREATE TABLE "rigdoc"(RdoIdoc INTEGER NOT NULL,RdoCcau TEXT NOT NULL COLLATE NOCASE,RdoCdep TEXT NOT NULL COLLATE NOCASE,RdoCart TEXT NOT NULL COLLATE NOCASE,RdoDesc TEXT NOT NULL COLLATE NOCASE,RdoCumi TEXT NOT NULL COLLATE NOCASE,RdoCiva TEXT NOT NULL COLLATE NOCASE,RdoCven TEXT NOT NULL COLLATE NOCASE,RdoQu00 REAL NOT NULL,RdoQu01 REAL NOT NULL,RdoQu02 REAL NOT NULL,RdoQu03 REAL NOT NULL,RdoQu04 REAL NOT NULL,RdoQuat REAL NOT NULL,RdoPrez REAL NOT NULL,RdoLord REAL NOT NULL,RdoSc00 REAL NOT NULL,RdoSc01 REAL NOT NULL,RdoSc02 REAL NOT NULL,RdoNett REAL NOT NULL,RdoTpro INTEGER NOT NULL,RdoProv REAL NOT NULL,RdoIdri INTEGER NOT NULL,RdoIdor INTEGER NOT NULL,RdoDtsc DATEINT NOT NULL,RdoCcad TEXT NOT NULL COLLATE NOCASE,RdoCded TEXT NOT NULL COLLATE NOCASE,RdoCard TEXT NOT NULL COLLATE NOCASE,RdoQuad REAL NOT NULL,RdoVarb INTEGER NOT NULL,RdoNupa TEXT NOT NULL COLLATE NOCASE,RdoTipo INTEGER NOT NULL,RdoTe00 INTEGER NOT NULL,RdoTe01 INTEGER NOT NULL,RdoTe02 INTEGER NOT NULL,RdoTe03 INTEGER NOT NULL,RdoTe04 INTEGER NOT NULL,RdoTe05 INTEGER NOT NULL,RdoTe06 INTEGER NOT NULL,RdoTe07 INTEGER NOT NULL,RdoTe08 INTEGER NOT NULL,RdoTe09 INTEGER NOT NULL,RdoNote TEXT NOT NULL COLLATE NOCASE,PRIMARY KEY(RdoIdoc)); CREATE TABLE "tesdoc"(TdoIdoc INTEGER NOT NULL,TdoTipo INTEGER NOT NULL,TdoAnno INTEGER NOT NULL,TdoCatt TEXT NOT NULL COLLATE NOCASE,TdoCdep TEXT NOT NULL COLLATE NOCASE,TdoSeri TEXT NOT NULL COLLATE NOCASE,TdoNdoc INTEGER NOT NULL,TdoDt00 DATEINT NOT NULL,TdoDt01 DATEINT NOT NULL,TdoDt02 DATEINT NOT NULL,TdoDt03 DATEINT NOT NULL,TdoDt04 DATEINT NOT NULL,TdoCleg TEXT NOT NULL COLLATE NOCASE,TdoCcau TEXT NOT NULL COLLATE NOCASE,TdoTcau INTEGER NOT NULL,TdoTsog TEXT NOT NULL COLLATE NOCASE,TdoCsog TEXT NOT NULL COLLATE NOCASE,TdoCval TEXT NOT NULL COLLATE NOCASE,TdoCamb REAL NOT NULL,TdoDtca DATEINT NOT NULL,TdoCpag TEXT NOT NULL COLLATE NOCASE,TdoDtip DATEINT NOT NULL,TdoCban TEXT NOT NULL COLLATE NOCASE,TdoCage TEXT NOT NULL COLLATE NOCASE,TdoScch REAL NOT NULL,TdoImsc REAL NOT NULL,TdoTpro INTEGER NOT NULL,TdoProv REAL NOT NULL,TdoNcol REAL NOT NULL,TdoPesl REAL NOT NULL,TdoPesn REAL NOT NULL,TdoDtit DATEINT NOT NULL,TdoNcop INTEGER NOT NULL,TdoAnpa INTEGER NOT NULL,TdoNupa INTEGER NOT NULL,TdoCocf TEXT NOT NULL COLLATE NOCASE,TdoNrec INTEGER NOT NULL,TdoNpri INTEGER NOT NULL,TdoCo00 TEXT NOT NULL COLLATE NOCASE,TdoCo01 TEXT NOT NULL COLLATE NOCASE,TdoCo02 TEXT NOT NULL COLLATE NOCASE,TdoCo03 TEXT NOT NULL COLLATE NOCASE,TdoCo04 TEXT NOT NULL COLLATE NOCASE,TdoCo05 TEXT NOT NULL COLLATE NOCASE,TdoCo06 TEXT NOT NULL COLLATE NOCASE,TdoCo07 TEXT NOT NULL COLLATE NOCASE,TdoCo08 TEXT NOT NULL COLLATE NOCASE,TdoCo09 TEXT NOT NULL COLLATE NOCASE,TdoCo10 TEXT NOT NULL COLLATE NOCASE,TdoCo11 TEXT NOT NULL COLLATE NOCASE,TdoCo12 TEXT NOT NULL COLLATE NOCASE,TdoCo13 TEXT NOT NULL COLLATE NOCASE,TdoCo14 TEXT NOT NULL COLLATE NOCASE,TdoCo15 TEXT NOT NULL COLLATE NOCASE,TdoCo16 TEXT NOT NULL COLLATE NOCASE,TdoCo17 TEXT NOT NULL COLLATE NOCASE,TdoCo18 TEXT NOT NULL COLLATE NOCASE,TdoCo19 TEXT NOT NULL COLLATE NOCASE,TdoSp00 REAL NOT NULL,TdoSp01 REAL NOT NULL,TdoSp02 REAL NOT NULL,TdoSp03 REAL NOT NULL,TdoSp04 REAL NOT NULL,TdoTo00 REAL NOT NULL,TdoTo01 REAL NOT NULL,TdoTo02 REAL NOT NULL,TdoTo03 REAL NOT NULL,TdoTo04 REAL NOT NULL,TdoTe00 INTEGER NOT NULL,TdoTe01 INTEGER NOT NULL,TdoTe02 INTEGER NOT NULL,TdoTe03 INTEGER NOT NULL,TdoTe04 INTEGER NOT NULL,TdoTe05 INTEGER NOT NULL,TdoTe06 INTEGER NOT NULL,TdoTe07 INTEGER NOT NULL,TdoTe08 INTEGER NOT NULL,TdoTe09 INTEGER NOT NULL,TdoRifi TEXT NOT NULL COLLATE NOCASE,TdoRife TEXT NOT NULL COLLATE NOCASE,TdoNote TEXT NOT NULL COLLATE NOCASE,TdoIrif INTEGER NOT NULL,TdoFdoc TEXT NOT NULL COLLATE NOCASE,PRIMARY KEY(TdoIdoc)); /* No STAT tables available */ Thanks in advance, Luigi. 2014-10-23 18:23 GMT+02:00 Richard Hipp <d...@sqlite.org>: > On Thu, Oct 23, 2014 at 11:55 AM, Luigi Iemma <iemmalu...@gmail.com> > wrote: > > > Hi, > > > > SELECT TdoIdoc,RdoCart,RdoQuat > > FROM Tesdoc > > INNER JOIN Rigdoc ON RdoIdoc BETWEEN TdoIdoc*10000000 AND > > TdoIdoc*10000000+9999999 > > WHERE TdoTipo=60 AND TdoAnno BETWEEN 2014 AND 2014 > > GROUP BY TdoIdoc > > > > When I run this query on 3.8.5 it takes 0.126 seconds, > > when I run this query on 3.8.7 it takes 17.37 seconds > > > > Please run the ".fullschema" command from the command-line shell and send > us the output. > > > > > if I change 'INNER JOIN Rigdoc' with 'LEFT JOIN Rigdoc' it takes 0.063 > > seconds > > > > the table Rigdoc have column RdoIdoc as PRIMARY KEY > > > > > > Best regards > > Luigi. > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > -- > D. Richard Hipp > d...@sqlite.org > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
