On: Avoiding “OR” for Better Query Performance

[gwd]

sqlite certainly seems to optimize it automaticaly; Note the 'IN operator' in the plan where none was in the query:

    sqlite> explain select * from words where wid=1 or wid=2;
    addr  opcode         p1    p2    p3    p4             p5  comment
    ----  -------------  ----  ----  ----  -------------  --  -------------
    0     Init           0     21    0                    00  Start at 21
    1     OpenRead       0     3     0     2              00  root=3 iDb=0; words
    2     Integer        11    2     0                    00  r[2]=11; return address
    3     Once           0     11    0                    00
    4     OpenEphemeral  1     1     0     k(1,)          00  nColumn=1; RHS of IN operator
    5     Integer        1     3     0                    00  r[3]=1
    6     MakeRecord     3     1     4     D              00  r[4]=mkrec(r[3])
    7     IdxInsert      1     4     3     1              00  key=r[4]
    8     Integer        2     3     0                    00  r[3]=2
    9     MakeRecord     3     1     4     D              00  r[4]=mkrec(r[3])
    10    IdxInsert      1     4     3     1              00  key=r[4]
    11    Return         2     0     0                    00
    12    Rewind         1     20    0                    00
    13      Column         1     0     1                    00  r[1]=
    14      IsNull         1     19    0                    00  if r[1]==NULL goto 19
    15      SeekRowid      0     19    1                    00  intkey=r[1]
    16      Rowid          0     5     0                    00  r[5]=rowid
    17      Column         0     1     6                    00  r[6]=words.text
    18      ResultRow      5     2     0                    00  output=r[5..6]
    19    Next           1     13    0                    00
    20    Halt           0     0     0                    00
    21    Transaction    0     0     97    0              01  usesStmtJournal=0
    22    Goto           0     1     0                    00

I've verified that "where wid in (1,2)" produces the same plan.