Tags

A database view is “the result set of a stored query on the data, which the database users can query just as they would in a persistent database collection object” (Source: Wikipedia).

The MySQL implementation is straightforward as can be seen from this database schema:

————————
SET SQL_MODE=”NO_AUTO_VALUE_ON_ZERO”;

CREATE TABLE `customer` (
`customer_id` int(11) NOT NULL auto_increment,
`customer_name` varchar(30) character set utf8 default NULL,
PRIMARY KEY (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=4 ;

CREATE TABLE `sales` (
`transaction_id` int(11) NOT NULL auto_increment,
`amount` int(11) default NULL,
`customer_id` int(11) NOT NULL,
PRIMARY KEY (`transaction_id`),
KEY `customer_id` (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=6 ;
CREATE TABLE `total_sales` (
`customer_name` varchar(30)
,`total` decimal(32,0)
);DROP TABLE IF EXISTS `total_sales`;

CREATE ALGORITHM=UNDEFINED DEFINER=`admin`@`localhost` SQL SECURITY DEFINER VIEW `total_sales` AS select `C`.`customer_name` AS `customer_name`,sum(`S`.`amount`) AS `total` from (`customer` `C` join `sales` `S` on((`C`.`customer_id` = `S`.`customer_id`))) group by `C`.`customer_id`;

ALTER TABLE `sales`
ADD CONSTRAINT `sales_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`);
————————

The view can be added as a database query:

CREATE VIEW total_sales AS SELECT customer_name, sum(amount) AS total FROM customer AS C JOIN sales AS S ON C.customer_id = S.customer_id GROUP BY C.customer_id;

A view resembles a temporary table and works just like any database table. This will work

SELECT * FROM total_sales;

But a delete

DELETE FROM `test`.`total_sales` WHERE `total_sales`.`customer_name` = ‘John’ AND CONCAT( `total_sales`.`total` ) = ’39’ LIMIT 1

will generate

#1288 – The target table total_sales of the DELETE is not updatable

Any update in one of the regular tables like

INSERT INTO sales SET amount = 2, customer_id = 1;

will result in an immediate update of the view.

In this example the algorithm (the way MySQL processes the view) is undefined. With this default setting it is left up to MySQL to decide what algorithm is most suitable. Views are especially effective with joined queries.

Are MySQL views a drain on performance? In a real-life example a view was added to the production database of http://assessmentee.com with the purpose of identifying bad performing questions:

CREATE VIEW statistics AS SELECT question_text, ( sum(score) / count(*)) * 100 as average, exam_score.question_id FROM exam_score
JOIN exam_question ON exam_question.question_id = exam_score.question_id
WHERE category = 1 and active = 1 group by question_id order by average ASC;

The production database holds over half a million scores but a slowdown in performance is not apparent.

Advertisements