MySQL Bugs: #60417: slow VIEWS by using subselect as column

Bug #60417	slow VIEWS by using subselect as column
Submitted:	10 Mar 2011 15:28	Modified:	27 Mar 2015 2:54
Reporter:	Richard Teubel	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S4 (Feature request)
Version:	5.1.51, 5.5.8	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	FUNCTION, slow, subselect, VIEW

Description:
Hello,

I have a VIEW performance problem if I use a subselect beside slow functions as list of columns. If the subselect in a function then the performanc is ok. In any case the "subselect-column" is not in the VIEW-Query.

mysql> SELECT ID
    -> FROM `ViewWithoutSubselect`;
+----+
| ID |
+----+
|  1 |
 ....
|  5 |
+----+
5 rows in set (0.00 sec)

mysql> SELECT ID
    -> FROM `ViewWithSubselect`;
+----+
| ID |
+----+
|  1 |
 ....
|  5 |
+----+
5 rows in set (5.01 sec) <===!!

How to repeat:
CREATE TABLE IF NOT EXISTS `myTable` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `myGroup` int(11) DEFAULT NULL,
  `myContent` varchar(20) DEFAULT NULL,
  `myDate` date DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `Group` (`myGroup`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=821 ;

INSERT INTO `myTable` (`ID`, `myGroup`, `myContent`, `myDate`) VALUES
(1, 2, '+=Z\\X:B|d:mK #5mc', '2011-03-01'),
(2, 2, '', '2011-03-01'),
(3, 2, 'u/RJI$SJ%T2G+|,e', '2011-03-01'),
(4, 2, 'q=7yfy''Ttb:u\\S8h];', '2011-03-01'),
(5, 2, '*J5eBaS', '2011-03-01');

CREATE TABLE IF NOT EXISTS `myTable2` (
  `Datum` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci;

INSERT INTO `myTable2` (`Datum`) VALUES
('2011-03-12');

DELIMITER //;

CREATE FUNCTION `slowFunction`(
        pi_ID INTEGER(11)
    )
    RETURNS int(11)
    DETERMINISTIC
    READS SQL DATA
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
  Select sleep(1) INTO @testtest;
  SET @myCount = @myCount+1;
  RETURN @myCount;
END //;

CREATE FUNCTION `get_myDate`()
    RETURNS date
    DETERMINISTIC
    READS SQL DATA
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
  DECLARE myDate DATE;
  
  SELECT myTable2.`Datum`
  INTO myDate
  FROM myTable2
  LIMIT 1;
  
  RETURN myDate;
END//;

CREATE SQL SECURITY DEFINER VIEW `ViewWithSubselect` AS 
  select 
    `myTable`.`ID` AS `ID`,
    `myTable`.`myGroup` AS `myGroup`,
    `myTable`.`myContent` AS `myContent`,
    (
  select 
    `myTable2`.`Datum` 
  from 
    `myTable2` limit 1) AS `myDate`,`slowFunction`(NULL) AS `SlowFunction` 
  from 
    `myTable`//;

CREATE SQL SECURITY DEFINER VIEW `ViewWithoutSubselect` AS 
  select 
    `myTable`.`ID` AS `ID`,
    `myTable`.`myGroup` AS `myGroup`,
    `myTable`.`myContent` AS `myContent`,
    `get_myDate`() AS `myDate`,
    `slowFunction`(NULL) AS `SlowFunction` 
  from 
    `myTable`//;

DELIMITER ;

SELECT ID
FROM `ViewWithSubselect`;

SELECT ID
FROM `ViewWithoutSubselect`;

show variables like "%version%";

I think the following EXPLAIN results:

mysql> explain SELECT ID FROM `ViewWithSubselect`;
+----+-------------+------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+------+-------+
|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |    5 |       |
|  2 | DERIVED     | mytable    | ALL  | NULL          | NULL | NULL    | NULL |    5 |       |
|  3 | SUBQUERY    | mytable2   | ALL  | NULL          | NULL | NULL    | NULL |    1 |       |
+----+-------------+------------+------+---------------+------+---------+------+------+-------+
3 rows in set (5.04 sec)

mysql> explain SELECT ID FROM `ViewWithoutSubselect`;
+----+-------------+---------+-------+---------------+-------+---------+------+------+-------------+
| id | select_type | table   | type  | possible_keys | key   | key_len | ref  | rows | Extra       |
+----+-------------+---------+-------+---------------+-------+---------+------+------+-------------+
|  1 | SIMPLE      | mytable | index | NULL          | Group | 5       | NULL |    5 | Using index |
+----+-------------+---------+-------+---------------+-------+---------+------+------+-------------+
1 row in set (0.00 sec)

explain the situation. In case of subselect optimizer has to "materialize" the result of select (derived table), and call slow function every time in the process. Without subselect optimizer just access ID from the base table using index and, thus, does not call slow function.

Time to execute EXPLAIN shows that this materialization actually happens at optimization stage, not at query execution stage. Optimizer has to use TEMPTABLE algorithm for this view, and this is described in the manual actually (see http://dev.mysql.com/doc/refman/5.5/en/view-algorithms.html):

"If the MERGE algorithm cannot be used, a temporary table must be used instead. MERGE cannot be used if the view contains any of the following constructs:

    - Aggregate functions (SUM(), MIN(), MAX(), COUNT(), and so forth)
    ...
    - Subquery in the select list
..."

So I think that this is a feature request to process view as MERGE in this case, but not a bug formally.

Thanks for your answer. I change the severity to feature request.

So, we have a valid feature request here for processing views differently (as MERGE) in case when subselect-based column from the view is NOT really accessed.

Noted in 5.7.7, 5.8.0 changelogs.

Previously, derived tables (subqueries) or views in the FROM clause
could not be merged into the outer query if they contained subqueries
of their own. Instead, they were processed using materialization.
This restriction has now been lifted so that queries previously
executed using materialization can be executed more quickly using
merging.

Revised changelog entry:

Previously, derived tables (subqueries) or views in the FROM clause could not be merged into the outer query if they contained subqueries in the SELECT list. Instead, they were processed using materialization. This restriction has now been lifted so that queries previously executed using materialization can be executed more quickly using merging.