21

I've done countless searches on materialized views and SQLite. Of what I can find there seems to be mentions in 2004 and 2006 that SQLite DOES NOT have materialized views. Followed immediately by SQLite's changelog from March 2008 where it specifically mentions optimizing materialized views.

Now, I figure logically either the 2004 and 2006 are outdated, or the 2008 changelog is wrong.

Any idea which it is?

If materialized views ARE now in SQLite, how are they created?

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
McAden
  • 13,714
  • 5
  • 37
  • 63

1 Answers1

23

I'd say what other DBMSes call "materialized views" are not supported, and what that ChangeLog meant is what MySQL's EXPLAIN would call a "filesort"; from the tempfiles page:

2.5 Materializations Of Views And Subqueries

Queries that contain subqueries must sometime evaluate the subqueries separately and store the results in a temporary table, then use the content of the temporary table to evaluate the outer query. We call this "materializing" the subquery. [...]

lapo
  • 3,136
  • 26
  • 34
  • 2
    Nobody else has any input and your assertion is the only thing I can find that puts this into any clarity. Thanks :) – McAden Sep 10 '09 at 21:58
  • I think you could create a proxy API outside the database which could hash the query and then put results into a hash table for subsequent calls to the same query. The hash table would need to not only store the results but the _table dependencies_ so that cache is invalidated if underlying data changes. You'd also need to ensure that this proxy is managing an efficient set of pooled connections to DB to that you're not losing performance to get a cache. I'm sure there are some additional complexities i'm not thinking of but it sounds like a fun little weekend project to try out. – ken May 04 '22 at 21:11