0

What would be the best way to achieve the features of a MySQL VIEW but not have it run the underlying query every time?

In essence, I want to store the result of a query (which aggregates a lot of data) as a new table and give it a name so that I can query that table directly. Do I need to create the table and use INSERT with a SELECT statement? Or is there a feature similar to a VIEW which can take the resulting table and simply store it with whatever name I give it?

poppe
  • 599
  • 6
  • 13
  • You can [`SELECT` directly into a tempdb table](https://stackoverflow.com/questions/1524858/create-table-variable-in-mysql). **Edit:** table variables aren't a thing in `MySQL`, my bad. – D M Feb 02 '21 at 14:18
  • See this for a discussion of "summary tables", which are very closely related to "materialized views", which is what you are asking for: http://mysql.rjweb.org/doc.php/summarytables – Rick James Feb 02 '21 at 21:12

1 Answers1

1

You can store the result of query in a temporary table:

CREATE TEMPORARY TABLE mytable SELECT ... FROM realtable ...;

The temp table really stores a copy of the result from that SELECT. So you can query it repeatedly, reading the result without invoking the costly query.

Temporary tables are scoped to the current session. So other sessions cannot read the temp table created in your session. Any temp tables you create are automatically dropped when your session ends.

The name is also scoped to your session. Your session can create a temp table with a name without worrying that another session might have created its own temp table of the same name.

If you want a table that is shared between sessions, and that can persist after your session ends, then use a regular table.

See also: https://dev.mysql.com/doc/refman/8.0/en/create-temporary-table.html

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828