SQLite Forum

SELECT optimization for constant expression
Login
I might be ignorant, but is not this query:

```
SELECT * FROM person
WHERE (?1 IS NULL OR firstname LIKE ?1)
AND (?2 IS NULL OR lastname LIKE ?2)
AND (?3 IS NULL OR birthdate == ?3)
AND (?4 IS NULL OR maidenname LIKE ?4)
ORDER BY lastname, firstname;
```

the same as this more appropriate should-be-index-using query:

```
SELECT * FROM person
 WHERE firstname LIKE ?1
    OR lastname LIKE ?2
    OR birthdate == ?3
    OR maidenname LIKE ?4
    OR (?1 IS NULL AND ?2 IS NULL AND ?3 IS NULL AND ?4 IS NULL)
ORDER BY lastname, firstname;
```

You could even leave out the final OR based on whether you would want to show No rows when no filters are set, or All rows in that case.

I have not tested the assumptions here, so I could be wrong about it.