MySQL Materialized Views

There are no materialized views in MySQL or MariaDB, but there are ways to achieve the same performance results in MySQL and MariaDB.

A while back I wrote an extensive article describing 3 Methods for Creating MySQL Materialized Views.

In this short article I will only discuss one of them, but please check out my extended article to see which method is best for your use case.

I am assuming that I have a big number of separate databases with similar structure and I have to query the data from all databases. Doing this with a normal query will be awful from a performance point of view.

Create MySQL materialized views using MySQL scheduled events

MySQL events are actions that you can schedule in MySQL. In our case the solution is to schedule the transfer of data between the source and the "materialized view".

You can schedule events to run every hour, every day, every week etc.

To read more on the syntax for creating a scheduled event please read the MySQL events syntax documentation.

In MySQL scheduled events are not activated by default.

To activate them do this

SET GLOBAL event_scheduler = ON;

or start your MySQL instance with the flag

–event-scheduler=ENABLED

CREATE EVENT `user_stats_daily` 
  ON SCHEDULE EVERY 1 DAY STARTS '2016-06-29 20:00:00' 
  ON COMPLETION NOT PRESERVE ENABLE 
  COMMENT 'Creates a materialized view with user data' DO BEGIN
        SET GLOBAL group_concat_max_len=1000000;
    select GROUP_CONCAT(CONCAT("SELECT NOW() as last_update,'",shard.name,"' as shardname,
                `name` 'Client name', 
                DATE_FORMAT(IFNULL(`lastLoginDate`,`loginDate`),'%b %d %Y %h:%i %p') 'Last login date', 
                lastLoginIP 'Login IP', 
                active_ 'Active' FROM `DB-", shard.name,
        "_portal`.`organization_`,`DB-",
        shard.name,
        "_portal`.`user_` WHERE `organizationId` in 
                  (select min(organizationId) from `DB-",shard.name,
        "_portal`.`organization_`) 
        ") SEPARATOR ' UNION ALL 
        ') INTO @stmt_sql
        FROM `portal_db`.`shard` shard;


SET @drop_stmt = "drop table if exists DB-APP._b_user_stats;";

 PREPARE stmt
FROM @drop_stmt;

 EXECUTE stmt;


SET @sql = concat("create table DB-APP._b_user_stats as ",@stmt_sql);

 PREPARE stmt
FROM @sql;

 EXECUTE stmt;

 DEALLOCATE PREPARE stmt;

 END

Let’s analyze what we have here.

First we define the scheduler to run every day at 20:00 (after business hours to avoid slowing the server down).

I used GROUP_CONCAT to dynamically create the SQL query. The query goes in all 200 databases so it is a pretty long string.

That is why we have to do SET GLOBAL group_concat_max_len=1000000; to set the maximum length of the text that GROUP_CONCAT can output.

The ‘shard’ table holds the index of each database. They are numbers 1 through 200, so the SQL will go over all databases from DB-1 to DB-200.

We store the text of the big SQL query in the variable @stmt_sql. Then we delete the “materialized view” to make sure we start fresh, and then create it again using the result of the big SQL query.

You now know another method to create MySQL materialized views.

It’s actually the one I used in production. Having the data daily updated was enough for the dashboard display.

Pros

This approach has the upside that it’s much easier to maintain than option 1. There is only one definition of the scheduled event to maintain.

You can easily adjust the frequency of the scheduler.

Cons

This method can affect the performance of the database if the scheduler runs too often. I runs the queries regularly even if no data was changed.