On Tue, 26 Jul 2016 10:37:12 +0200 Alessandro Fardin <alessandro.far...@gavazziacbu.it> 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 a dramatically slow down of the > application. > > 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 > > Follows the shema of the table and indexes. > CREATE INDEX IF NOT EXISTS energy_d_dateTimeIdx > ON hst_energy_d (recdate ASC,rectime ASC,idinstrum ASC,enflag ASC); > CREATE INDEX IF NOT EXISTS hst_energy_d_index_timestamp > ON hst_energy_d (timestamp ASC); > CREATE INDEX IF NOT EXISTS hst_energy_d_index_pupdate > ON hst_energy_d (pupdate ASC); > Please, run Sql commnad ANALYZE after index creation and chek again. > And now the SELECT STATEMENT > > > SELECT > pupdate,idinstrum,Vlnsys,Vl1n,Vl2n,Vl3n,Vllsys,Vl1l2,Vl2l3,Vl3l1, > Al1,Al2,Al3,kWsys,kWl1,kWl2,kWl3,kWhac, > kWhacn,kvarsys,kvarl1,kvarl2,kvarl3,kvarhn,kvarh, > kvarhacC,kvarhacL,kVAsys,kVAl1,kVAl2 ,kVAl3,PSeq, > THDAl1,THDAl2,THDAl3,THDVl1n,THDVl2n,THDVl3n, > kWhl1 ,kWhl2 ,kWhl3 ,counter1,counter2,counter3,Hz,An,Hour,Hourn, > Alsys,kvarhl1,kvarhl2,kvarhl3,kvarhnl1,kvarhnl2,kvarhnl3,kWhnl1,kWhnl2, > kWhnl3,kVAh,kVAhl1,kVAhl2,kVAhl3, > PFsys,PFl1,PFl2,PFl3,Wdmd,vardmd,VAdmd > FROM hst_energy_d > WHERE enflag = 0 ORDER BY recdate DESC, rectime DESC LIMIT 1; You are querying with ORDER BY DESC, but indexes are created ASC, convert them to DESC, ANALYZE and retry. HTH --- --- Eduardo Morras <emorr...@yahoo.es> _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
