SQLite Forum

SELECT optimization for constant expression
Login
```
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;
```

This query will be challenging indeed.

You really want the results in order so there's a strong tension between 

* using the index to get the order and filtering those results so that no transient index needs to be created

* trying to use indices for selectivity

But the problem is there is only one query plan allowed for the one query... and you might need 4  several different indices. Plus the QP doesn't know that the arguments will be of the form "x%" so its not likely to be tempted to use indices for that.

IS this http://cgsql.dev that you're using and if so do you have other constraints like maybe exactly one of those filters is specified?

Another strategy you could try depending on the selectivity is something like this:

```
SELECT * FROM person where firstname like 1?
union
SELECT * FROM person where lastname like 2?
union
SELECT * FROM person where birthdate = ?3
union
SELECT * FROM person where maidenname like ?4
order by lastname, firstname
```

Which might be better... assuming you don't really want all the rows if you specify none of the patterns.

It would be better still if you knew that only one of those is specified.  In CG-SQL (CQL) you can do.

```
create proc getmystuff(fname TEXT, lname TEXT, bdate LONG, mname TEXT)
begin
  if fname is not null then
    SELECT * FROM person where firstname like fname 
    order by lastname, firstname;
  else if lname is not null then    
    SELECT * FROM person where lastname like lname
    order by lastname, firstname;
  else if bdate is not null then
    SELECT * FROM person where birthdate = bdate
    order by lastname, firstname;
  else if mname is not null then
    SELECT * FROM person where maidenname like mname
    order by lastname, firstname;
  end if;
end;
```

But that only works if you know that exactly one criteria will be specified.

And I'm not sure you're using the CG-SQL that I'm thinking of :D