MySQL Materialized Views | Overview

Let's briefly go over what MySQL materialized views are, how they function, and how to use them. As part of our Server Management Services, Skynats responds to all of your queries, no matter how big or small.

What Are Materialized Views?

The pre-calculated outcomes of a query, which are typically kept in a table, are known as materialized views. While a regular MySQL query would take a while to process, these object types are very helpful when you require an instant response. By itself, MySQL does not have default settings. However, creating Materialized Views is simple.

Refreshing Materialized Views occasionally may be necessary depending on the content, the requirements, and the frequency of the refresh. A Materialized View refresh can be delayed to a full or specific period or performed immediately.

Implementing MySQL materialized views

Here is a quick example of how this might be accomplished:

SELECT COUNT(*)
  FROM MyISAM_table;

Because the counter is stored in the table header, it produces quick results.

Let's use a different example to make a table that stores all InnoDB row counts.

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
);

The table may be updated once per day, based on the necessary accuracy of this data.

The data could also be retrieved from the schema information, as another option.

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

Make MySQL materialized views of the events that are scheduled

Events in MySQL are specific types of actions that can be planned. The data transfer between the source and the "materialized view" can be scheduled. The planned actions can be carried out every hour, every day, every week, and even every month. By default, the MySQL scheduled events are not turned on. Even so, by including the following syntax, this can be activated:

SET GLOBAL event_scheduler = ON;

A different approach is to start MySQL with the flag:

event-scheduler=ENABLED

How to Create MySQL scheduled event syntax

Let's examine creating a scheduled event in MySQL

To prevent the server from going down, it is best to suggest running the scheduler after business hours.

You can use GROUP CONCAT, which can fetch a large number of databases, to create SQL queries on the fly.

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 index of each database is kept in the "samp" table. They range from 1 to 200, or DB-1 to DB-200, meaning that the SQL will cover all databases.

The large SQL query's text is stored as a variable by the tag @stmt sql. Once created, the "materialized view" can be deleted to ensure that we start over and recreate it using the output of the large SQL query.