On 26 Feb 2014, at 9:09pm, pihu...@free.fr wrote: > Benchmark (bench.php) on the « $bdd->query(...); » instruction : > Query 2 : select DateMonteeAuPlan, Debut, Fin, Statut from ReportJobs where > NomJob = 'NSAVBASE' and NomChaine like 'DCLC257%' limit 20; > => 0.00099992752075195 seconde(s) > > Query 3 : select DateMonteeAuPlan, Debut, Fin, Statut from ReportJobs where > NomJob = 'NSAVBASE' and NomChaine = 'DCLC25736' order by DateMonteeAuPlan > DESC limit 20; > => 0 seconde(s) > > Query 1 : select DateMonteeAuPlan, Debut, Fin, Statut from ReportJobs where > NomJob = 'NSAVBASE' and NomChaine like 'DCLC257%' order by DateMonteeAuPlan > DESC limit 20; > => 1.8629999160767 seconde(s) > > > Why is there so much differences between two query quasi-identical?
My guess is that withthe combination of a LIKE and another condition, the optimizer can't figure out which approach will give you the fastest result. First, do an ANALYZE on that database. You can do it inside your own software or just open the database with the SQLite shell tool. The results of the ANALYZE are saved in the database for later use. If that doesn't improve things try using the command EXPLAIN QUERY PLAN [select command goes here] on each of those SELECT statements. You'll see which indexes the statement is trying to use. Again you can execute this command in your own software or in the shell tool. The results are returned as if you had done a SELECT command. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
