If you replace "between x and x" (which is just a very inefficient method of testing for equality) with "= x" the query reverts to the same plan as "IN (x,...)" with the small difference that the constant values are assigned to registers first.
-----Ursprüngliche Nachricht----- Von: Dominique Pellé [mailto:[email protected]] Gesendet: Mittwoch, 15. Mai 2013 22:28 An: General Discussion of SQLite Database Betreff: [sqlite] Missing optimization with id BETWEEN xxx AND xxx? Hi The SQL script at http://dominique.pelle.free.fr/query-between.sql shows 2 SELECT queries: * Select query #1 takes 20.2 sec (slow!) * Select query #2 takes 0.004 sec (fast!) Yet the 2 queries are equivalent. Here is how to download and run the script: ===================================== $ wget http://dominique.pelle.free.fr/query-between.sql $ rm -f foo.sqlite ; sqlite3 foo.sqlite < query-between.sql --- Populating table... --- Query #1: measure time to select with 150 BETWEEN clauses with equal min/max -> SLOW! 0 CPU Time: user 20.237265 sys 0.036003 <--- SLOW! --- Query #2: measure time to select using: IN (...150 values...) --> equivalent and FAST! 0 CPU Time: user 0.004001 sys 0.000000 <--- FAST! ===================================== If I add "EXPLAIN QUERY PLAN" in front of each SELECT query, I see: --- Query #1: measure time to select with 150 BETWEEN clauses with equal min/max -> SLOW! 0|0|0|SCAN TABLE t (~500000 rows) --- Query #2: measure time to select using: IN (...150 values...) --> equivalent and FAST! 0|0|0|SEARCH TABLE t USING INTEGER PRIMARY KEY (rowid=?) (~150 rows) 0|0|0|EXECUTE LIST SUBQUERY 1 Shouldn't SQLite be able to use the index (implicit index since 'id' is a PRIMARY KEY) when using BETWEEN clauses? I understand that using... "WHERE id BETWEEN xxx AND xxx OR id BETWEEN yyy AND yyy" ... is a bit silly. It's of course simpler to write the equivalent... "WHERE id IN (xxx, yyy)" However, I found this query which was automatically generated. Not all ranges have equal min/max but often they do. It was slow. Replacing all the equal ranges using "OR id IN (xxx, yyy...)" resulted in a big speed up. Can't the SQLite optimizer do such optimization automatically? I'm using SQLite 3.7.16.2 2013-04-12 11:52:43 on Linux x86_64. Regards Dominique _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -------------------------------------------------------------------------- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: [email protected] This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
