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:
Triage: Needs Triage: D3 (Medium)

[19 Nov 2012 11:52] Sveta Smirnova
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
[19 Nov 2012 11:54] Sveta Smirnova
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 |
[19 Nov 2012 11:54] Sveta Smirnova
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
[19 Nov 2012 11:56] Sveta Smirnova
database dump

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

[9 Mar 2015 19:03] Roy Lyseng
Fixed in 5.7.7