Re: [sqlite] OR, IN: which is faster?
Thank you , Joe Wilson. 2007/2/3, Joe Wilson <[EMAIL PROTECTED]>: --- chueng alex1985 <[EMAIL PROTECTED]> wrote: > I don't think so. If the field has been indexed, searching speed will be > imprved very much. The query "f1 = value1 OR f1 = value2 OR f1=value3 OR > ..." will be faster if the field f1 has been indexed. On the other hand, the > clause "f1 in (value1, value2, value3, ...)" seems to be slower then 'OR' > clause because i think it will get the all value of 'f1' and check whether > the f1's value is in (value1, value2, value3, ...). If the table has 100K > records, it will compare 100K times, in contrast, the OR clause only need > compare few times because of the index when the number of values in (value1, > value2, value3, ...) list is not too big. As drh pointed out, if you run the following in sqlite 3.3.12 you'll see both the IN and the OR queries produce the exact same instructions, resulting in the same query speed if an index is used: CREATE TABLE abc(a,b,c); CREATE INDEX abc_c on abc(c); explain select * from abc where c in (11,22,33); explain select * from abc where c=11 or c=22 or c=33; (same output for both) 0|Goto|0|41| 1|Integer|0|0| 2|OpenRead|0|2| 3|SetNumColumns|0|3| 4|Integer|0|0| 5|OpenRead|1|3|keyinfo(1,BINARY) 6|MemLoad|2|0| 7|If|0|20| 8|MemInt|1|2| 9|OpenEphemeral|2|0|keyinfo(1,BINARY) 10|SetNumColumns|2|1| 11|Integer|11|0| 12|MakeRecord|1|0|b 13|IdxInsert|2|0| 14|Integer|22|0| 15|MakeRecord|1|0|b 16|IdxInsert|2|0| 17|Integer|33|0| 18|MakeRecord|1|0|b 19|IdxInsert|2|0| 20|Rewind|2|38| 21|Column|2|0| 22|IsNull|-1|37| 23|MemStore|1|1| 24|MemLoad|1|0| 25|MakeRecord|1|0|b 26|MemStore|0|0| 27|MoveGe|1|37| 28|MemLoad|0|0| 29|IdxGE|1|37|+ 30|IdxRowid|1|0| 31|MoveGe|0|0| 32|Column|0|0| 33|Column|0|1| 34|Column|0|2| 35|Callback|3|0| 36|Next|1|28| 37|Next|2|21| 38|Close|0|0| 39|Close|1|0| 40|Halt|0|0| 41|Transaction|0|0| 42|VerifyCookie|0|2| 43|Goto|0|1| 44|Noop|0|0| If you drop in the index abc_c, you will see the behavior you mentioned. The OR query is on the left, and the IN query is on the right: 0|Goto|0|21| | 0|Goto|0|36| 1|Integer|0|0| 1|Integer|0|0| 2|OpenRead|0|2| 2|OpenRead|0|2| 3|SetNumColumns|0|3|3|SetNumColumns|0|3| 4|Rewind|0|19|| 4|Rewind|0|34| 5|Column|0|2| | 5|MemLoad|0|0| 6|Integer|11|0| | 6|If|0|19| 7|Eq|98|14|collseq(BINARY)| 7|MemInt|1|0| 8|Column|0|2| | 8|OpenEphemeral|1|0|keyinfo(1,BINARY) 9|Integer|22|0| | 9|SetNumColumns|1|1| 10|Eq|98|14|collseq(BINARY) | 10|Integer|11|0| 11|Column|0|2|| 11|MakeRecord|1|0|b 12|Integer|33|0| | 12|IdxInsert|1|0| 13|Ne|354|18|collseq(BINARY) | 13|Integer|22|0| 14|Column|0|0|| 14|MakeRecord|1|0|b 15|Column|0|1|| 15|IdxInsert|1|0| 16|Column|0|2|| 16|Integer|33|0| 17|Callback|3|0| | 17|MakeRecord|1|0|b 18|Next|0|5| | 18|IdxInsert|1|0| 19|Close|0|0| | 19|Integer|1|0| 20|Halt|0|0| | 20|Column|0|2| 21|Transaction|0|0| | 21|NotNull|-1|25| 22|VerifyCookie|0|3| | 22|Pop|2|0| 23|Goto|0|1| | 23|Null|0|0| 24|Noop|0|0| | 24|Goto|0|28| > 25|MakeRecord|1|0|b > 26|Found|1|28| > 27|AddImm|-1|0| > 28|IfNot|1|33| > 29|Column|0|0| > 30|Column|0|1| > 31|Column|0|2| > 32|Callback|3|0| > 33|Next|0|5| > 34|Close|0|0| > 35|Halt|0|0| > 36|Transaction|0|0| > 37|VerifyCookie|0|3| > 38|Goto|0|1| > 39|Noop|0|0| You can mimic the index-less OR behavior on the column (even in the presence of such an index) by putting a plus before each column in the where clause, thus disqualifying the column from using an index: select * from abc where +c=11 or +c=22 or +c=33 This "OR" query table scan might be faster than using an index in cases where you know that you will be selecting the majority of the rows in the table. > > 2007/2/1, [EMAIL PROTECTED] <[EMAIL PROTECTED]>: > > > > Ion Silvestru <[EMAIL PROTECTED]> wrote: > > > If we have a query where we compare a column to a set of values, then > > > which is faster: OR or IN? > > > Ex: OR: (mycol = "a") OR (mycol = "b")
Re: [sqlite] OR, IN: which is faster?
--- chueng alex1985 <[EMAIL PROTECTED]> wrote: > I don't think so. If the field has been indexed, searching speed will be > imprved very much. The query "f1 = value1 OR f1 = value2 OR f1=value3 OR > ..." will be faster if the field f1 has been indexed. On the other hand, the > clause "f1 in (value1, value2, value3, ...)" seems to be slower then 'OR' > clause because i think it will get the all value of 'f1' and check whether > the f1's value is in (value1, value2, value3, ...). If the table has 100K > records, it will compare 100K times, in contrast, the OR clause only need > compare few times because of the index when the number of values in (value1, > value2, value3, ...) list is not too big. As drh pointed out, if you run the following in sqlite 3.3.12 you'll see both the IN and the OR queries produce the exact same instructions, resulting in the same query speed if an index is used: CREATE TABLE abc(a,b,c); CREATE INDEX abc_c on abc(c); explain select * from abc where c in (11,22,33); explain select * from abc where c=11 or c=22 or c=33; (same output for both) 0|Goto|0|41| 1|Integer|0|0| 2|OpenRead|0|2| 3|SetNumColumns|0|3| 4|Integer|0|0| 5|OpenRead|1|3|keyinfo(1,BINARY) 6|MemLoad|2|0| 7|If|0|20| 8|MemInt|1|2| 9|OpenEphemeral|2|0|keyinfo(1,BINARY) 10|SetNumColumns|2|1| 11|Integer|11|0| 12|MakeRecord|1|0|b 13|IdxInsert|2|0| 14|Integer|22|0| 15|MakeRecord|1|0|b 16|IdxInsert|2|0| 17|Integer|33|0| 18|MakeRecord|1|0|b 19|IdxInsert|2|0| 20|Rewind|2|38| 21|Column|2|0| 22|IsNull|-1|37| 23|MemStore|1|1| 24|MemLoad|1|0| 25|MakeRecord|1|0|b 26|MemStore|0|0| 27|MoveGe|1|37| 28|MemLoad|0|0| 29|IdxGE|1|37|+ 30|IdxRowid|1|0| 31|MoveGe|0|0| 32|Column|0|0| 33|Column|0|1| 34|Column|0|2| 35|Callback|3|0| 36|Next|1|28| 37|Next|2|21| 38|Close|0|0| 39|Close|1|0| 40|Halt|0|0| 41|Transaction|0|0| 42|VerifyCookie|0|2| 43|Goto|0|1| 44|Noop|0|0| If you drop in the index abc_c, you will see the behavior you mentioned. The OR query is on the left, and the IN query is on the right: 0|Goto|0|21| | 0|Goto|0|36| 1|Integer|0|0| 1|Integer|0|0| 2|OpenRead|0|2| 2|OpenRead|0|2| 3|SetNumColumns|0|3|3|SetNumColumns|0|3| 4|Rewind|0|19|| 4|Rewind|0|34| 5|Column|0|2| | 5|MemLoad|0|0| 6|Integer|11|0| | 6|If|0|19| 7|Eq|98|14|collseq(BINARY)| 7|MemInt|1|0| 8|Column|0|2| | 8|OpenEphemeral|1|0|keyinfo(1,BINARY) 9|Integer|22|0| | 9|SetNumColumns|1|1| 10|Eq|98|14|collseq(BINARY) | 10|Integer|11|0| 11|Column|0|2|| 11|MakeRecord|1|0|b 12|Integer|33|0| | 12|IdxInsert|1|0| 13|Ne|354|18|collseq(BINARY) | 13|Integer|22|0| 14|Column|0|0|| 14|MakeRecord|1|0|b 15|Column|0|1|| 15|IdxInsert|1|0| 16|Column|0|2|| 16|Integer|33|0| 17|Callback|3|0| | 17|MakeRecord|1|0|b 18|Next|0|5| | 18|IdxInsert|1|0| 19|Close|0|0| | 19|Integer|1|0| 20|Halt|0|0| | 20|Column|0|2| 21|Transaction|0|0| | 21|NotNull|-1|25| 22|VerifyCookie|0|3| | 22|Pop|2|0| 23|Goto|0|1| | 23|Null|0|0| 24|Noop|0|0| | 24|Goto|0|28| > 25|MakeRecord|1|0|b > 26|Found|1|28| > 27|AddImm|-1|0| > 28|IfNot|1|33| > 29|Column|0|0| > 30|Column|0|1| > 31|Column|0|2| > 32|Callback|3|0| > 33|Next|0|5| > 34|Close|0|0| > 35|Halt|0|0| > 36|Transaction|0|0| > 37|VerifyCookie|0|3| > 38|Goto|0|1| > 39|Noop|0|0| You can mimic the index-less OR behavior on the column (even in the presence of such an index) by putting a plus before each column in the where clause, thus disqualifying the column from using an index: select * from abc where +c=11 or +c=22 or +c=33 This "OR" query table scan might be faster than using an index in cases where you know that you will be selecting the majority of the rows in the table. > > 2007/2/1, [EMAIL PROTECTED] <[EMAIL PROTECTED]>: > > > > Ion Silvestru <[EMAIL PROTECTED]> wrote: > > > If we have a query where we compare a column to a set of values, then > > > which is faster: OR or IN? > > > Ex: OR: (mycol = "a") OR (mycol = "b") OR (mycol = "c") OR... > > > IN: (mycol IN "a", "b", "c"
Re: [sqlite] OR, IN: which is faster?
I don't think so. If the field has been indexed, searching speed will be imprved very much. The query "f1 = value1 OR f1 = value2 OR f1=value3 OR ..." will be faster if the field f1 has been indexed. On the other hand, the clause "f1 in (value1, value2, value3, ...)" seems to be slower then 'OR' clause because i think it will get the all value of 'f1' and check whether the f1's value is in (value1, value2, value3, ...). If the table has 100K records, it will compare 100K times, in contrast, the OR clause only need compare few times because of the index when the number of values in (value1, value2, value3, ...) list is not too big. 2007/2/1, [EMAIL PROTECTED] <[EMAIL PROTECTED]>: Ion Silvestru <[EMAIL PROTECTED]> wrote: > If we have a query where we compare a column to a set of values, then > which is faster: OR or IN? > Ex: OR: (mycol = "a") OR (mycol = "b") OR (mycol = "c") OR... > IN: (mycol IN "a", "b", "c" ...) > IN is faster. However, version 3.2.3 introduced an enhancement to the SQLite optimizer that automatically converts the OR form of the expression above into the IN form, thus taking advantage of the increased speed of IN. So for SQLite version 3.2.3, there really is no difference between the two. See http://www.sqlite.org/optoverview.html#or_opt -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - -- powered by python
Re: [sqlite] OR, IN: which is faster?
Ion Silvestru <[EMAIL PROTECTED]> wrote: > If we have a query where we compare a column to a set of values, then > which is faster: OR or IN? > Ex: OR: (mycol = "a") OR (mycol = "b") OR (mycol = "c") OR... > IN: (mycol IN "a", "b", "c" ...) > IN is faster. However, version 3.2.3 introduced an enhancement to the SQLite optimizer that automatically converts the OR form of the expression above into the IN form, thus taking advantage of the increased speed of IN. So for SQLite version 3.2.3, there really is no difference between the two. See http://www.sqlite.org/optoverview.html#or_opt -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] OR, IN: which is faster?
On Feb 1, 2007, at 8:19 AM, Ion Silvestru wrote: If we have a query where we compare a column to a set of values, then which is faster: OR or IN? Ex: OR: (mycol = "a") OR (mycol = "b") OR (mycol = "c") OR... IN: (mycol IN "a", "b", "c" ...) IN is much faster - OR disables any use of indexes for column mycol. Cheers, Peter. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] OR, IN: which is faster?
If we have a query where we compare a column to a set of values, then which is faster: OR or IN? Ex: OR: (mycol = "a") OR (mycol = "b") OR (mycol = "c") OR... IN: (mycol IN "a", "b", "c" ...) Thanks. - To unsubscribe, send email to [EMAIL PROTECTED] -
