MySQL Bugs: #67631: Subquery in view works slower than if run without view

Bug #67631	Subquery in view works slower than if run without view
Submitted:	19 Nov 2012 11:52	Modified:	9 Mar 2015 19:03
Reporter:	Sveta Smirnova	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.0.97, 5.1.67, 5.5.29, 5.7.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Based on this blog post: http://ru-mysql.livejournal.com/287623.html

If you create a view with subquery, this view will be executed slower than if run same query, but without view.

How to repeat:
CREATE TABLE IF NOT EXISTS `A` (
  `id` int(11) NOT NULL,
  `v1` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `B` (
  `id` int(11) NOT NULL,
  `v2` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Insert random data.

SELECT A.id, A.v1, (SELECT v2 FROM B WHERE B.id<=A.id ORDER BY B.id DESC LIMIT 1) as v2m FROM A LIMIT 0,30

This query executes in  0.0001 seconds.

Then create a view:

create view C (id, v1, v2m) as
SELECT A.id,
    A.v1,
    (SELECT v2 FROM B WHERE B.id<=A.id ORDER BY B.id DESC LIMIT 1) as v2m
    FROM A

Select from the view and wait for hours:

SELECT * FROM C LIMIT 0, 30

Explain for select from view:

mysql [localhost] {msandbox} (test) > explain extended SELECT * FROM C LIMIT 0, 30;
+----+--------------------+------------+-------+---------------+---------+---------+------+--------+-----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+------------+-------+---------------+---------+---------+------+--------+-----------+-------------+
| 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 140140 | 100.00 | |
| 2 | DERIVED | A | ALL | NULL | NULL | NULL | NULL | 140140 | 100.00 | |
| 3 | DEPENDENT SUBQUERY | t1 | index | PRIMARY | PRIMARY | 4 | NULL | 1 | 739000.00 | Using where |
+----+--------------------+------------+-------+---------------+---------+---------+------+--------+-----------+-------------+
3 rows in set, 2 warnings (39 min 32.48 sec)

Note (Code 1276): Field or reference 'test.A.id' of SELECT #3 was resolved in SELECT #2
Note (Code 1003): select `C`.`id` AS `id`,`C`.`v1` AS `v1`,`C`.`v2m` AS `v2m` from `test`.`C` limit 0,30

mysql> show status like 'Handler%';
+----------------------------+-----------+
| Variable_name | Value |
+----------------------------+-----------+
...
| Handler_read_prev | 586832829 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 183832 |
...
| Handler_write | 183831 |
+----------------------------+-----------+
15 rows in set (0.00 sec)

This status is for EXPLAIN in version 5.5.

And, finally, show status like 'Handler%'; for a query without view:

| Handler_read_prev | 221630 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 30 |

Workaround:

create view C (id, v1, v2m) as
SELECT A.id,
    A.v1,
    (SELECT v2 FROM B WHERE B.id<=A.id ORDER BY B.id DESC LIMIT 1) as v2m
    FROM A
    LIMIT 0, 30

database dump

Attachment: asdasd.sql.gz (application/x-gzip, text), 405.53 KiB.

Fixed in 5.7.7