How can I prepare a query with variable amounts of WHERE expressions?

Question

I have a query that looks like this:

SELECT
  sn.name,           -- Street name
  sa.house_number,   -- House number
  sa.entrance,       -- Entrance
  pc.postal_code,    -- Postal code
  ci.name,           -- City
  mu.name,           -- Municipality
  co.name            -- County
FROM
  street_addresses AS sa
    INNER JOIN street_names   AS sn ON sa.street_name  = sn.id
    INNER JOIN postal_codes   AS pc ON sa.postal_code  = pc.id
    INNER JOIN cities         AS ci ON sa.city         = ci.id
    INNER JOIN municipalities AS mu ON sa.municipality = mu.id
    INNER JOIN counties       AS co ON mu.county       = co.id
WHERE
  (:id           IS NULL OR sa.id           = :id) AND
  (:street_name  IS NULL OR sn.name         = :street_name) AND
  (:house_number IS NULL OR sa.house_number = :house_number) AND
  (:entrance     IS NULL OR sa.entrance     = :entrance) AND
  (:postal_code  IS NULL OR pc.postal_code  = :postal_code) AND
  (:city         IS NULL OR ci.name         = :city) AND
  (:municipality IS NULL OR mu.name         = :municipality) AND
  (:county       IS NULL OR co.name         = :county)
ORDER BY
  sn.name ASC, sa.house_number ASC, sa.entrance ASC

The query looks this stupid because I want to be able to filter by any of the columns in the WHERE section, as long as the rest of the column filters are NULL. For instance, I can search for an address using the above query by passing {street_name: "foo", house_number: 12} or {postal_code: 1234, house_number: 5} to the same prepared statement, as long as the rest of the keys are set to nil.

The problem with this query is that SQLite3 apparently fails to use the database indexes. This query only runs between 6 and 8 times per second. If I replace the WHERE section with WHERE sa.house_number = ? AND sn.name = ? and prepare the query, it runs over 110 000 times per second.

I could build the query dynamically for every time it's run, but introducing that much work for every search, plus losing the ability to use prepared statements, reduces the speed of the query to ~4000 runs per second.

Summary:

Is there any way to achieve the effect of the above query while convincing SQLite3 to use the indexes for speed optimisation?

Answer 1

With that many OR-connected terms, using indexes efficiently is not possible.

You should definitely use dynamic query strings. If you think that preparing those takes too long, build a cache of prepared statements, with the query string as key. (Prepared statements use very little memory.)