How can a materialized view be created in sqlite?

sql sqlite materialized-views
McAden picture McAden · Sep 3, 2009 · Viewed 8.2k times · Source

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?

Answer

lapo picture lapo · Sep 3, 2009

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. [...]

Related questions

Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More
How to list the tables in a SQLite database file that was opened with ATTACH?

What SQL can be used to list the tables, and the rows within those tables in an SQLite database file - once I have attached it with the ATTACH command on the SQLite 3 command line tool?

Read More
Is it possible to insert multiple rows at a time in an SQLite database?

In MySQL you can insert multiple rows like this: INSERT INTO 'tablename' ('column1', 'column2') VALUES ('data1', 'data2'), ('data1', 'data2'), ('data1', 'data2'), ('data1', 'data2'); However, I am getting an error when …

Read More