Hello everyone!

We have been investing plenty of personal time and energy for many years to share our knowledge with you all. However, we now need your help to keep this blog running. All you have to do is just click one of the adverts on the site, otherwise it will sadly be taken down due to hosting etc. costs. Thank you.

Materialised view (MV) is a table which holds specifically selected data from one or more tables. You can see them in your database just like normal tables. They are used to prevent performance dips as much as possible. It can be refreshed whenever it is required but in most cases result set will be outdated compared to original tables. You can query MVs the way you would do to normal tables. Note: Unique constraints on fields in materialised view tables may produce results in wrong order so it is suggested not to use any uniqueness on any fields.


Dummy tables


We're going to deal with tables user and activity below when working with MVs.


User table


CREATE TABLE `user` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
`password` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `username_unique` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO user
(username, password)
VALUES
('one_username', 'one_password'),
('two_username', 'two_password'),
('three_username', 'three_password'),
('four_username', 'four_password'),
('five_username', 'five_password');

mysql> SELECT * FROM user;
+----+----------------+----------------+
| id | username | password |
+----+----------------+----------------+
| 1 | one_username | one_password |
| 2 | two_username | two_password |
| 3 | three_username | three_password |
| 4 | four_username | four_password |
| 5 | five_username | five_password |
+----+----------------+----------------+
5 rows in set (0.00 sec)

Activity table


CREATE TABLE `activity` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`ip` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
`timestamp` datetime NOT NULL,
`url` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`user_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `fk_activity_1_idx` (`user_id`),
CONSTRAINT `fk_activity_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO activity
(ip, timestamp, url, user_id)
VALUES
('1.1.1.1', '2015-04-01 18:10:59', '/url_one', 1),
('1.1.1.1', '2015-04-02 10:06:11', '/url_two', 1),
('2.2.2.2', '2015-04-01 21:00:00', '/url_one', 2),
('2.2.2.2', '2015-04-03 23:01:40', '/url_one', 2),
('3.3.3.3', '2015-04-02 02:23:30', '/url_one', 3),
('4.4.4.4', '2015-04-01 03:00:14', '/url_two', 4),
('5.5.5.5', '2015-04-01 13:30:33', '/url_one', 5);

mysql> SELECT * FROM activity;
+----+---------+---------------------+----------+---------+
| id | ip | timestamp | url | user_id |
+----+---------+---------------------+----------+---------+
| 1 | 1.1.1.1 | 2015-04-01 18:10:59 | /url_one | 1 |
| 2 | 1.1.1.1 | 2015-04-02 10:06:11 | /url_two | 1 |
| 3 | 2.2.2.2 | 2015-04-01 21:00:00 | /url_one | 2 |
| 4 | 2.2.2.2 | 2015-04-03 23:01:40 | /url_one | 2 |
| 5 | 3.3.3.3 | 2015-04-02 02:23:30 | /url_one | 3 |
| 6 | 4.4.4.4 | 2015-04-01 03:00:14 | /url_two | 4 |
| 7 | 5.5.5.5 | 2015-04-01 13:30:33 | /url_one | 5 |
+----+---------+---------------------+----------+---------+
7 rows in set (0.00 sec)

Create and query materialised view on a single table


Actual table version


mysql> SELECT * FROM user ORDER BY username;
+----+----------------+----------------+
| id | username | password |
+----+----------------+----------------+
| 5 | five_username | five_password |
| 4 | four_username | four_password |
| 1 | one_username | one_password |
| 3 | three_username | three_password |
| 2 | two_username | two_password |
+----+----------------+----------------+
5 rows in set (0.04 sec)

Materialised view version


# Create a MV table.
CREATE TABLE `user_mv` (
`id` int(10) unsigned NOT NULL,
`username` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
`password` varchar(40) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

# Populate MV with ordered data.
INSERT INTO user_mv
SELECT
id,
username,
password
FROM user
ORDER BY username;

# As you can see, we're not using ORDER BY at all.
mysql> SELECT * FROM user_mv;
+----+----------------+----------------+
| id | username | password |
+----+----------------+----------------+
| 5 | five_username | five_password |
| 4 | four_username | four_password |
| 1 | one_username | one_password |
| 3 | three_username | three_password |
| 2 | two_username | two_password |
+----+----------------+----------------+
5 rows in set (0.00 sec)

Create and query materialised view on joined tables


Actual table version


mysql> SELECT
-> user.username,
-> activity.timestamp,
-> activity.url
-> FROM user
-> INNER JOIN activity ON activity.user_id = user.id
-> ORDER BY
-> user.username ASC,
-> activity.timestamp DESC;
+----------------+---------------------+----------+
| username | timestamp | url |
+----------------+---------------------+----------+
| five_username | 2015-04-01 13:30:33 | /url_one |
| four_username | 2015-04-01 03:00:14 | /url_two |
| one_username | 2015-04-02 10:06:11 | /url_two |
| one_username | 2015-04-01 18:10:59 | /url_one |
| three_username | 2015-04-02 02:23:30 | /url_one |
| two_username | 2015-04-03 23:01:40 | /url_one |
| two_username | 2015-04-01 21:00:00 | /url_one |
+----------------+---------------------+----------+
7 rows in set (0.00 sec)

Materialised view version


# Create a MV table.
CREATE TABLE `user_activity_mv` (
`username` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
`timestamp` datetime NOT NULL,
`url` varchar(255) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

# Populate MV with ordered data.
INSERT INTO user_activity_mv
SELECT
user.username,
activity.timestamp,
activity.url
FROM user
INNER JOIN activity ON activity.user_id = user.id
ORDER BY
user.username ASC,
activity.timestamp DESC;

# As you can see, we're not using ORDER BY at all.
mysql> SELECT * FROM user_activity_mv;
+----------------+---------------------+----------+
| username | timestamp | url |
+----------------+---------------------+----------+
| five_username | 2015-04-01 13:30:33 | /url_one |
| four_username | 2015-04-01 03:00:14 | /url_two |
| one_username | 2015-04-02 10:06:11 | /url_two |
| one_username | 2015-04-01 18:10:59 | /url_one |
| three_username | 2015-04-02 02:23:30 | /url_one |
| two_username | 2015-04-03 23:01:40 | /url_one |
| two_username | 2015-04-01 21:00:00 | /url_one |
+----------------+---------------------+----------+
7 rows in set (0.00 sec)

Updating materialised views


It's up to you how to and how often update materialised views. You can use stored procedures, triggers, cron jobs or do manual updates.


Stored procedure


# Current status of MV.
mysql> SELECT * FROM user_mv;
+----+----------------+----------------+
| id | username | password |
+----+----------------+----------------+
| 5 | five_username | five_password |
| 4 | four_username | four_password |
| 1 | one_username | one_password |
| 3 | three_username | three_password |
| 2 | two_username | two_password |
+----+----------------+----------------+
5 rows in set (0.00 sec)

# Create the stored procedure with different ORDER BY clause.
DROP PROCEDURE IF EXISTS user_mv_sp;

DELIMITER $$

CREATE PROCEDURE user_mv_sp()
BEGIN
TRUNCATE TABLE user_mv;

INSERT INTO user_mv
SELECT
id,
username,
password
FROM user
ORDER BY id DESC;
END;
$$

DELIMITER ;

# Call stored procedure.
mysql> CALL user_mv_sp();
Query OK, 5 rows affected (0.12 sec)

# Check the new status of MV.
mysql> SELECT * FROM user_mv;
+----+----------------+----------------+
| id | username | password |
+----+----------------+----------------+
| 5 | five_username | five_password |
| 4 | four_username | four_password |
| 3 | three_username | three_password |
| 2 | two_username | two_password |
| 1 | one_username | one_password |
+----+----------------+----------------+
5 rows in set (0.00 sec)

Triggers (after insert, delete and update)


# Current status of MV.
mysql> SELECT * FROM user_mv;
+----+----------------+----------------+
| id | username | password |
+----+----------------+----------------+
| 5 | five_username | five_password |
| 4 | four_username | four_password |
| 3 | three_username | three_password |
| 2 | two_username | two_password |
| 1 | one_username | one_password |
+----+----------------+----------------+
5 rows in set (0.00 sec)

# Create an "After Insert" trigger on "user" table.
DROP TRIGGER IF EXISTS after_insert;

DELIMITER $$

CREATE TRIGGER after_insert
AFTER INSERT ON user
FOR EACH ROW
BEGIN
INSERT INTO user_mv
(username, password)
VALUES
(NEW.id, NEW.username, NEW.password);
END;
$$

DELIMITER ;

# Insert a record to "user" table.
INSERT INTO user (username, password) VALUES ('six_username', 'six_password');

# Check the new status of MV.
mysql> SELECT * FROM user_mv;
+----+----------------+----------------+
| id | username | password |
+----+----------------+----------------+
| 5 | five_username | five_password |
| 4 | four_username | four_password |
| 3 | three_username | three_password |
| 2 | two_username | two_password |
| 1 | one_username | one_password |
| 6 | six_username | six_password |
+----+----------------+----------------+
6 rows in set (0.00 sec)

# Create an "After Delete" trigger on "user" table.
DROP TRIGGER IF EXISTS after_delete;

DELIMITER $$

CREATE TRIGGER after_delete
AFTER DELETE ON user
FOR EACH ROW
BEGIN
DELETE FROM user_mv
WHERE id = OLD.id;
END;
$$

DELIMITER ;

# Delete a record from "user" table.
DELETE FROM user WHERE id = 6;

# Check the new status of MV.
mysql> SELECT * FROM user_mv;
+----+----------------+----------------+
| id | username | password |
+----+----------------+----------------+
| 5 | five_username | five_password |
| 4 | four_username | four_password |
| 3 | three_username | three_password |
| 2 | two_username | two_password |
| 1 | one_username | one_password |
+----+----------------+----------------+
5 rows in set (0.00 sec)

# Create an "After Update" trigger on "user" table.
DROP TRIGGER IF EXISTS after_update;

DELIMITER $$

CREATE TRIGGER after_update
AFTER UPDATE ON user
FOR EACH ROW
BEGIN
UPDATE user_mv
SET
username = NEW.username,
password = NEW.password
WHERE
id = OLD.id;
END;
$$

DELIMITER ;

# Update a record in "user" table.
UPDATE user SET password = 'one_passwords' WHERE id = 1;

# Check the new status of MV.
mysql> SELECT * FROM user_mv;
+----+----------------+----------------+
| id | username | password |
+----+----------------+----------------+
| 5 | five_username | five_password |
| 4 | four_username | four_password |
| 3 | three_username | three_password |
| 2 | two_username | two_password |
| 1 | one_username | one_passwords |
+----+----------------+----------------+
5 rows in set (0.00 sec)