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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
