Materialized views with MySQL

Question

Emulated materialized views with MySQL has good performance? I'm learning how to do with this link

thanks

---

Correction: "Materialized views" to "Emulated materialized views".

Answer 1

A materialized view is just a fancy name for a normal table with the data from some heavy query.

So although creating it is just as heavy as the heavy query itself, querying it is really fast.

The big question here is how you want to update the view.

• You can do a regular full refresh. Simple to do, but heavy during that update and in between updates the data is outdated.
• You can use triggers to automatically update the data when inserting/deleting/updating. That makes the inserts/deletes/updates for your other tables slightly heavier, but it won't be outdated.

Answer 2

MySQL doesn't have materialized views - the link just creates a table and stuffs data into it so the table can be indexed. That means the performance is par with a normal table, but you also have the overhead of flushing & repopulating the table (including indexes).

I didn't see what engine the table was using, but MEMORY would likely be a better choice.