MySQL Bugs: #59819: TEMPTABLE Views are Extremely Slow

Bug #59819	TEMPTABLE Views are Extremely Slow
Submitted:	30 Jan 2011 11:03	Modified:	15 Jan 2013 20:42
Reporter:	Gus Welter	Email Updates:
Status:	Analyzing	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S5 (Performance)
Version:	5.1,5.5	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	temptable, Views

Description:
Views requiring the TEMPTABLE algorithm perform abysmally, taking seconds for a single query to execute.

How to repeat:
Create a view with multiple table joins and aggregate functions. Fill the tables up reasonably (say 100,000 rows each). Run a query against the view, even one with a primary key specification, and you will see the vast performance cost of this implementation.

Note that once a given query is run, it will be faster when run subsequently (so change the WHERE clause every time you run it).

Suggested fix:
Re-factor the implementation of TEMPTABLE views such that criteria in the WHERE clause are considered before building the entire View table.

Please, check if it is a duplicate of bug #59696.

I uploaded bug-data-59819.zip to help reproduce the problem.

The script will create tables t1 & t2 and view v1:

DROP TABLE IF EXISTS t2;
DROP TABLE IF EXISTS t1;
DROP VIEW IF EXISTS v1;

CREATE TABLE `mydb`.`t1` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
)
ENGINE = InnoDB;

CREATE TABLE `mydb`.`t2` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `t1_id` INTEGER UNSIGNED NOT NULL,
  `someval` INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `FK_t2_1` FOREIGN KEY `FK_t2_1` (`t1_id`)
    REFERENCES `t1` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
)
ENGINE = InnoDB;

CREATE OR REPLACE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `v1` AS
SELECT t1.id, AVG(t2.someval), SUM(t2.someval), GROUP_CONCAT(t2.someval) FROM t1, t2 WHERE t1.id = t2.t1_id GROUP BY t1.id;

Note that, while the View is defined with ALGORITHM=UNDEFINED, the engine will use TEMPTABLE because of the aggregate functions and group by statement. This could also be defined with ALGORITHM=TEMPTABLE.

The script will then load 100,000 records into t1 and 500,000 records into t2.

You can reproduce the performance problem by running the following queries and watching their execution times:

SELECT t1.id, AVG(t2.someval), SUM(t2.someval), GROUP_CONCAT(t2.someval) FROM t1, t2 WHERE t1.id = t2.t1_id GROUP BY t1.id;

SELECT * FROM v1;

The first query is the exact query defined in v1. The execution times I saw were:

Raw Query: 12.1 ms
View Query: 1685.6 ms

Valeriy, I believe this is not a duplicate of bug #59696 because the view in that bug is using the MERGE algorithm.

Hi Gus,

Thank you for the bug report!

I wanted to try repeating the same performance issues with MySQL 5.6, and if I could, then to mark this as verified.

However, I'm not able to find the referenced file, bug-data-59819.zip.

Could you please place this on the new/current FTP server for me?
ftp://ftp.oracle.com/support/incoming/

If you let me know when it's there, then I'll go ahead and try to verify the issue with the latest 5.6 release candidate.

Thank you!

SQL script to create db `mydb` and corresponding tables and views to reproduce bug 59819

Attachment: bug-data-59819.sql.zip (application/zip, text), 2.45 MiB.

Screenshot

Attachment: Screen Shot 2013-01-15 at 9.44.07 AM.png (image/png, text), 716.89 KiB.

Matthew, I had to reproduce the file as I had not saved it.

New filename: bug-data-59819.sql.zip

I also added a screenshot with results on version 5.5.28-0ubuntu0.12.04.3.

Hi Gus,

In 5.6, the WHERE clause is used to some effect. We can see this with:

mysql> explain extended select * from v1;
+----+-------------+------------+-------+---------------+---------+---------+------------+-------+----------+-------------+
| id | select_type | table      | type  | possible_keys | key     | key_len | ref        | rows  | filtered | Extra       |
+----+-------------+------------+-------+---------------+---------+---------+------------+-------+----------+-------------+
|  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL    | NULL    | NULL       | 99593 |   100.00 | NULL        |
|  2 | DERIVED     | t1         | index | PRIMARY       | PRIMARY | 4       | NULL       | 99593 |   100.00 | Using index |
|  2 | DERIVED     | t2         | ref   | FK_t2_1       | FK_t2_1 | 4       | mydb.t1.id |     1 |   100.00 | NULL        |
+----+-------------+------------+-------+---------------+---------+---------+------------+-------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

mysql> explain extended select * from v1 where id=99999;
+----+-------------+------------+-------+---------------+-------------+---------+------------+-------+----------+-------------+
| id | select_type | table      | type  | possible_keys | key         | key_len | ref        | rows  | filtered | Extra       |
+----+-------------+------------+-------+---------------+-------------+---------+------------+-------+----------+-------------+
|  1 | PRIMARY     | <derived2> | ref   | <auto_key0>   | <auto_key0> | 4       | const      |    10 |   100.00 | NULL        |
|  2 | DERIVED     | t1         | index | PRIMARY       | PRIMARY     | 4       | NULL       | 99593 |   100.00 | Using index |
|  2 | DERIVED     | t2         | ref   | FK_t2_1       | FK_t2_1     | 4       | mydb.t1.id |     1 |   100.00 | NULL        |
+----+-------------+------------+-------+---------------+-------------+---------+------------+-------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

mysql> select * from v1 where id=99999;
+-------+-----------------+-----------------+--------------------------+
| id    | AVG(t2.someval) | SUM(t2.someval) | GROUP_CONCAT(t2.someval) |
+-------+-----------------+-----------------+--------------------------+
| 99999 |         10.0000 |              50 | 0,5,10,15,20             |
+-------+-----------------+-----------------+--------------------------+
1 row in set (2.41 sec)

mysql> select * from v1 where id=99998;
+-------+-----------------+-----------------+--------------------------+
| id    | AVG(t2.someval) | SUM(t2.someval) | GROUP_CONCAT(t2.someval) |
+-------+-----------------+-----------------+--------------------------+
| 99998 |         10.0000 |              50 | 0,5,10,15,20             |
+-------+-----------------+-----------------+--------------------------+
1 row in set (2.36 sec)

Obviously it's still not as fast as running this:
mysql> explain select `t1`.`id` AS `id`,avg(`t2`.`someval`) AS `AVG(t2.someval)`,sum(`t2`.`someval`) AS `SUM(t2.someval)`,group_concat(`t2`.`someval` separator ',') AS `GROUP_CONCAT(t2.someval)` from (`t1` join `t2`) where (`t1`.`id` = `t2`.`t1_id`) AND t1.id = 99999 group by `t1`.`id`;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | t1    | const | PRIMARY       | PRIMARY | 4       | const |    1 | Using index |
|  1 | SIMPLE      | t2    | ref   | FK_t2_1       | FK_t2_1 | 4       | const |    5 | NULL        |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
2 rows in set (0.00 sec)

In order to do a pushdown of the WHERE clause to the actual VIEW query itself, we would need to have a "dynamic view"/parameterized view or a table valued function:
  http://msdn.microsoft.com/en-us/library/ms191165(v=sql.105).aspx

A normal view cannot take parameters, the query used to create the view is static. We can then only filter on the view itself and materialized views would almost certainly be a big help here, but there is already a feature request for that.

Would you like me to create a feature request for table valued functions, if I cannot find one already in existence?

Thanks!

Matt

Hi Matt,

If you classify it as a feature, I would say it's more a request to make it possible to use the MERGE algorithm when using aggregate functions, not be forced to use TEMPTABLE. The TEMPTABLE algorithm is really unusable when dealing with tables having more than a very small record count.

In the meantime, perhaps more visible warnings could be placed in MySQL's documentation warning of the serious performance limitations of the TEMPTABLE algorithm and, therefore, any views defined with aggregate functions. The only scenario where I could see the performance limitations are not an issue would be where the view will only be queried without WHERE clauses. Other than that, developers should should be warned to avoid writing views with aggregate functions at all cost.

I just re-scanned the Create View and View Processing Algorithms pages and didn't see anything prominent:

http://dev.mysql.com/doc/refman/5.0/en/create-view.html
http://dev.mysql.com/doc/refman/5.0/en/view-algorithms.html

Anyway, my two cents.

Many thanks,
Gus