Let’s quickly discuss what MySQL materialized views are, how they work, and how to implement them in MySQL. Bobcares answers all your questions, small or large as part of our MySQL support.

What Are Materialized Views?

Materialized views are the pre-calculated results of a query that is generally stored in a table. These object types are super useful when you need an immediate response in need, whereas a regular MySQL query would take a long time to process. MySQL does not have default values by itself. But you can easily build Materialized Views.

Materialized Views can be refreshed once in a while, it depends on the content and the requirements and how often refresh need. Basically, a Materialized View refresh can be done immediately or deferred to a full or to a certain point in time.

MySQL materialized views Implementation

A short example of how this could be done in the following query:

SELECT COUNT(*)
  FROM MyISAM_table;

it returns immediate results because the counter is stored in the table header.

Let’s take another example to create a table where all InnoDB row counts are stored in

CREATE TABLE innodb_row_count (
    id          INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
  , schema_name VARCHAR(64)  NOT NULL
  , table_name  VARCHAR(64)  NOT NULL
  , row_count   INT UNSIGNED NOT NULL
);

Depending on the required correctness of this information, the table can be refreshed once a day.

One more possibility would be to extract the data from the schema information.

SELECT table_schema, table_name, table_rows
  FROM information_schema.tables
 WHERE table_type = 'BASE TABLE';

Create MySQL materialized views of scheduled events

MySQL events are kind actions that can be scheduled in MySQL. You can schedule the transfer of data between the source and the “materialized view”. The scheduled events can be executed every hour, every day, every week, and months. The MySQL scheduled events are not active by default. However, this can be activated by adding the following syntax:

SET GLOBAL event_scheduler = ON;

another way is to start the MySQL with the flag:

event-scheduler=ENABLED

How to Create MySQL scheduled event syntax

Let’s analyze creating MySQL scheduled event.

First, we define a scheduler to run every day, the better suggestion is to run after business hours so that you can avoid the server down.

For the SQL query dynamically creation you can use GROUP_CONCAT because this has the capacity of fetching a high number of databases.

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,'",samp.name,"' as sampname,
                `name` 'Client name', 
                DATE_FORMAT(IFNULL(`lastLoginDate`,`loginDate`),'%b %d %Y %h:%i %p') 'Last login date', 
                lastLoginIP 'Login IP', 
                active_ 'Active' FROM `DB-", samp.name,
  "_portal`.`organization_`,`DB-",
  samp.name,
  "_portal`.`user_` WHERE `organizationId` in 
                  (select min(organizationId) from `DB-",samp.name,
  "_portal`.`organization_`) 
  ") SEPARATOR ' UNION ALL 
  ') INTO @stmt_sql
  FROM `portal_db`.`samp` samp;


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

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

The tag @stmt_sql is to store the text of the large SQL query as the variable. Once it is created, we can delete the “materialized view” so that just to make sure we start fresh and then create it again using the result of the big SQL query.

Conclusion

To conclude, MySQL materialized views can help to speed up queries, it is super easy to execute if there is a huge amount of data and the databases are not memory-based anymore.