Performance penalties from sqlite views

Question

I have a select query, as the following:

select distinct
  ...some fields

from
  table_a,
  table_b,
  table_c,
  ...more tables

where
  table_a.id = table_b.id and
  ... (rest of the tables) and
  some_field_that_i_care_about = 42

order by
  my_field

If I run that query as-is, it takes around half a second to get me the results that I expect.

Nevertheless, if I convert that same query to a view (excluding the some_field_that_i_care_about = 42 condition) and then I run:

select *
from the_view
where some_field_that_i_care_about = 42

the query takes around 40 seconds to return the same data.

Why is that happening?

Answer 1

Views are just syntactic sugar for subqueries. Such subqueries usually do not result in a performance penalty because they can be flattened, i.e., merged into the outer query.

However, in this case, the ORDER BY prevents the flattening.

Just drop the ORDER BY from the view; the ordering of the subquery is ignored by the outer query anyway. (If you want the result to be sorted, you must use ORDER BY in the outermost query.)