On 28 Feb 2014, at 9:48am, pihu...@free.fr wrote: > I did a fourth one with a '+' sign before the "NomJob = 'NSAVBASE'" clause > :[snip] > This one is way quicker (0.055 second instead of 2.235 seconds in my latest > test).
That is a good experiment. Unfortunately it depends on you knowing something special about SQLite you shouldn't have to know. > Is there a better way to optimize all my queries instead of checking them one > by one ? The honest answer to this is to learn how indexes work and are used. Theoretically the way to use SQL is to create one ideal index for each SELECT, DELETE and UPDATE command. Of course, it will turn out that many commands have the same 'ideal' index, or one index will make them both fast enough even if it is not the abolute fastest. Or that the best index for them is the one SQLite automatically creates on the TABLE's primary key. So in real life you don't end up with that many indexes. > I heard indexes aren't useful for a database with a single table. What do you > suggest ? Sorry, but what you heard is nonsense. Indexes operate /only/ on a single table. There's no way to put data from two tables in one index (assuming the documentation is correct and you can't create an index on a VIEW). I will try to give an answer useful for many people and I have to simplify this a little to make it fit in an email message. To learn to optimize a command like select DateMonteeAuPlan, Debut, Fin, Statut from ReportJobs where NomJob = 'NSAVBASE' and NomChaine like 'DCLC257%' order by DateMonteeAuPlan DESC limit 20 guess at many indexes like the following: CREATE INDEX ti1 ON ReportJobs (NomJob, NomChaine, DateMonteeAuPlan) CREATE INDEX ti2 ON ReportJobs (NomChaine, NomJob, DateMonteeAuPlan) ANALYZE EXPLAIN QUERY PLAN select DateMonteeAuPlan, [...] This would give SQLite its best possible chance of having a very good index to use. Then look at the output of the EXPLAIN QUERY PLAN command, see which index SQLite decided to use, and delete the others. (just to explain: ANALYZE analyses how useful each index is, so if you create or delete indexes it can be useful to do ANALYZE again.) Once you have done this for a few projects you will learn how indexes work and you will just naturally be able to guess at a good index for each command, and also decide whether the increase in file size makes it worth creating an index when it would speed up a command by only 20 or 30 milliseconds. Of course, creating an index may speed up a search but it slows down changes made to a table (on each change, each index must be updated). So the correct thing to do depends on the nature of your program: whether you want input and output to be faster. And lastly the standard warning about over optimization: Remember that if you find yourself saying "My program must do everything as fast as possible." you are doing the wrong thing. It is better to say "My program must do everything fast enough.". Hope this helps. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
