Re: [sqlite] Missing optimization with id BETWEEN xxx AND xxx?
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:dominique.pe...@gmail.com] 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.00 <--- 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 (~50 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 -- 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: h...@scigames.at 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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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.00 <--- 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 (~50 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
Re: [sqlite] the Nth 32-bit int in byte array in query
sort by with substr? I hadn't thought of doing that.. Thanks! On Wed, May 15, 2013 at 5:38 AM, E. Timothy Uy wrote: > Scratch that - I realized I could use substr. > > > On Tue, May 14, 2013 at 11:57 PM, E. Timothy Uy wrote: > > > I'm using matchinfo(...,'s') to get the length of the longest subsequence > > of phrase matches from FTS4. I have 4 columns, so I end up with a byte > > array that is 16 bytes long. I would like to extract the value for the > Nth > > column in query and sort by it. I would also be interested in getting the > > Max() value. Is there a way of doing this without writing any additional > > functions? > > > > Thanks, > > Tim > > > ___ > 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
Re: [sqlite] the Nth 32-bit int in byte array in query
Scratch that - I realized I could use substr. On Tue, May 14, 2013 at 11:57 PM, E. Timothy Uy wrote: > I'm using matchinfo(...,'s') to get the length of the longest subsequence > of phrase matches from FTS4. I have 4 columns, so I end up with a byte > array that is 16 bytes long. I would like to extract the value for the Nth > column in query and sort by it. I would also be interested in getting the > Max() value. Is there a way of doing this without writing any additional > functions? > > Thanks, > Tim > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
