> And let us know if the problem persists. Perhaps this has been fixed by
>
> http://www.sqlite.org/src/info/e4b8a2ba6e
>
>
Richard,
I tried to investigate the problem L L posted in parallel. I think L L will
report his results. From my tests in seem the snapshot has fixed the
problem, but it seems the cause was different to one mentioned by you.
When I narrowed the query to
select * from catalogues
inner join
(select max(validfrom), idcatalogue from CRONOcatalogues) as tbl
on catalogues.idcatalogue=tbl.idcatalogue and catalogues.active='s'
The difference between the plans was the lines
TABLE catalogues
TABLE AS tbl WITH AUTOMATIC INDEX
in case of 3.7.0.1 and
TABLE AS tbl
TABLE catalogues USING PRIMARY KEY
in case of 3.23.
For some reason, sqlite 3.7.0.1 prefered creating automatic index on the
temporary result (tbl) and full scan catalogues while 3.23 full-scanned the
sub-query (or even feed the results while performing the sub-query) and used
primary index for catalogues. The snapshot you mentioned now correctly uses
the index. Mabye it was due to false assumption that the index can not be
used when a field not used in index (active = 's') exists in the
expression.
Max
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
