Re: [sqlite] sqlite 3.13.0 does not use indexes as 3.8.11.1 does

2016-07-28 Thread Alessandro Fardin
- From: Richard Hipp <d...@sqlite.org> To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Cc: alessandro.far...@gavazziacbu.it Date: 27/07/2016 21:33 Subject:Re: [sqlite] sqlite 3.13.0 does not use indexes as 3.8.11.1 does Sent b

Re: [sqlite] sqlite 3.13.0 does not use indexes as 3.8.11.1 does

2016-07-27 Thread Richard Hipp
On 7/26/16, Richard Hipp wrote: > > Two possible fixes are: > > (1) Disable the new query algorithm introduced in 3.12. This does not > fix the cost estimate for the 3.11 algorithm, but as there are no > other reasonable competing algorithm choices, the 3.11 algorithm will >

Re: [sqlite] sqlite 3.13.0 does not use indexes as 3.8.11.1 does

2016-07-27 Thread Richard Hipp
On 7/26/16, Alessandro Fardin wrote: > After updating from sqlite 3.8.11.1 to sqlite 3.13.0. > > The Query planner with the same SELECT statement on same table with the > same indexes does not use index at all, but parse the entire table. > Of course this causes

Re: [sqlite] sqlite 3.13.0 does not use indexes as 3.8.11.1 does

2016-07-26 Thread Richard Hipp
On 7/26/16, Richard Hipp wrote: > Consider: > > SELECT * FROM t1 WHERE b=99 AND c<>22 ORDER BY a LIMIT 1; > Or, how about this one: SELECT * FROM t1 WHERE appfunc(b)=99 AND c<>22 ORDER BY a LIMIT 1; In this case, SQLite has the option of doing a table look-up in order

Re: [sqlite] sqlite 3.13.0 does not use indexes as 3.8.11.1 does

2016-07-26 Thread Richard Hipp
On 7/26/16, Alessandro Fardin wrote: > > The Query planner with the same SELECT statement on same table with the > same indexes does not use index at all, but parse the entire table. > Of course this causes a dramatically slow down of the application. > When you

Re: [sqlite] sqlite 3.13.0 does not use indexes as 3.8.11.1 does

2016-07-26 Thread Quan Yong Zhai
SQLite version 3.14.0 2016-07-26 04:49:43 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create table t(x,y,z); sqlite> create index i1 on t(x,y); sqlite> explain query plan select * from t where y=100

Re: [sqlite] sqlite 3.13.0 does not use indexes as 3.8.11.1 does

2016-07-26 Thread Quan Yong Zhai
Query plan changed, It's a bug 发件人: Alessandro Fardin<mailto:alessandro.far...@gavazziacbu.it> 发送时间: ‎2016/‎7/‎26 16:37 收件人: sqlite-users@mailinglists.sqlite.org<mailto:sqlite-users@mailinglists.sqlite.org> 主题: [sqlite] sqlite 3.13.0 does not

Re: [sqlite] sqlite 3.13.0 does not use indexes as 3.8.11.1 does

2016-07-26 Thread Eduardo Morras
On Tue, 26 Jul 2016 10:37:12 +0200 Alessandro Fardin wrote: > After updating from sqlite 3.8.11.1 to sqlite 3.13.0. > > The Query planner with the same SELECT statement on same table with > the same indexes does not use index at all, but parse the entire >

Re: [sqlite] sqlite 3.13.0 does not use indexes as 3.8.11.1 does

2016-07-26 Thread Luca Ferrari
On Tue, Jul 26, 2016 at 10:37 AM, Alessandro Fardin wrote: > As temporary work around we have have added to the query the INDEXED BY > energy_d_dateTimeIdx statement. > > In sqlite 3.8.11.1 the select was issued by using the > energy_d_dateTimeIdx index Any

[sqlite] sqlite 3.13.0 does not use indexes as 3.8.11.1 does

2016-07-26 Thread Alessandro Fardin
After updating from sqlite 3.8.11.1 to sqlite 3.13.0. The Query planner with the same SELECT statement on same table with the same indexes does not use index at all, but parse the entire table. Of course this causes a dramatically slow down of the application. As temporary work around we have