Thanks Simon for your reply. An ANALYZE; in the SQLite shell tool did nothing performance-wise.
Here are the EXPLAIN QUERY PLAN results : sqlite> EXPLAIN QUERY PLAN select DateMonteeAuPlan, Debut, Fin, Statut from ReportJobs where NomJob = 'NSAVBASE' and NomChaine like 'DCLC257%' limit 20; 0|0|0|SEARCH TABLE ReportJobs USING INDEX sqlite_autoindex_ReportJobs_1 (ANY(NomChaine) AND ANY(DateMonteeAuPlan) AND NomJob=?) --> pdo_sqlite query() : 0 second sqlite> EXPLAIN QUERY PLAN select DateMonteeAuPlan, Debut, Fin, Statut from ReportJobs where NomJob = 'NSAVBASE' and NomChaine = 'DCLC25736' order by DateMonteeAuPlan DESC limit 20; 0|0|0|SEARCH TABLE ReportJobs USING INDEX sqlite_autoindex_ReportJobs_1 (NomChaine=?) --> pdo_sqlite query() : 0.001 second sqlite> EXPLAIN QUERY PLAN select DateMonteeAuPlan, Debut, Fin, Statut from ReportJobs where NomJob = 'NSAVBASE' and NomChaine like 'DCLC257%' order by DateMonteeAuPlan DESC limit 20; 0|0|0|SEARCH TABLE ReportJobs USING INDEX sqlite_autoindex_ReportJobs_1 (ANY(NomChaine) AND ANY(DateMonteeAuPlan) AND NomJob=?) 0|0|0|USE TEMP B-TREE FOR ORDER BY --> pdo_sqlite query() : 2.235 seconds I did a fourth one with a '+' sign before the "NomJob = 'NSAVBASE'" clause : sqlite> EXPLAIN QUERY PLAN select DateMonteeAuPlan, Debut, Fin, Statut from ReportJobs where +NomJob = 'NSAVBASE' and NomChaine like 'DCLC257%' order by DateMonteeAuPlan DESC limit 20; 0|0|0|SCAN TABLE ReportJobs 0|0|0|USE TEMP B-TREE FOR ORDER BY This one is way quicker (0.055 second instead of 2.235 seconds in my latest test). Is there a better way to optimize all my queries instead of checking them one by one ? I heard indexes aren't useful for a database with a single table. What do you suggest ? Thanks again. ----- Mail original ----- De: "Simon Slavin" <slav...@bigfraud.org> À: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> Envoyé: Jeudi 27 Février 2014 14:08:24 Objet: Re: [sqlite] 'Select' queries via pdo_sqlite are slow 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
