MySQL Bugs: #60508: Cache VIEW definitions

Bug #60508	Cache VIEW definitions
Submitted:	17 Mar 2011 11:37	Modified:	17 Mar 2011 23:33
Reporter:	Mattias Jiderhamn	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S5 (Performance)
Version:	5.5.21, 5.6.6	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
It seems that VIEWs as a concept, no matter how they are defined, are a performance bottleneck. From what I gather, there is no equivalent to table_open_cache or table_definition_cache, so there is a relatively high performance cost related to each access of a VIEW.

In my testing the difference is more obvious on Windows than Linux, with 3-5 times longer execution time, as compared to 50-100% more on Linux.

For more background information, see http://forums.mysql.com/read.php?24,411036,411036

How to repeat:
# Create database, table, view and data

CREATE DATABASE test;
USE test;
CREATE TABLE t (id INTEGER, PRIMARY KEY (id));
INSERT INTO t (id) VALUES (1);
CREATE ALGORITHM = MERGE VIEW v AS SELECT (id) FROM t;

# Profile

SET PROFILING=1;
SELECT id FROM t WHERE id = 1;
SELECT id FROM t WHERE id = 1; # Run again to use cache
SELECT id FROM v WHERE id = 1;
SELECT id FROM v WHERE id = 1; # Run again to use cache
SET PROFILING=0;

# Show result

SHOW PROFILES;

SHOW PROFILE FOR QUERY 2;
SHOW PROFILE FOR QUERY 4;

# Looking at the details of a profile, the real difference is in "Opening tables".

Suggested fix:
Add caches to keep VIEW definitions

Thank you for the bug report.

C:\DBS>c:\dbs\5.5\bin\mysql -uroot --port=3540 --prompt="mysql 5.5 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.5.11 Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.5 >CREATE DATABASE test;
Query OK, 1 row affected (0.00 sec)

mysql 5.5 >USE test;
Database changed
mysql 5.5 >CREATE TABLE t (id INTEGER, PRIMARY KEY (id));
Query OK, 0 rows affected (0.16 sec)

mysql 5.5 >INSERT INTO t (id) VALUES (1);
Query OK, 1 row affected (0.03 sec)

mysql 5.5 >CREATE ALGORITHM = MERGE VIEW v AS SELECT (id) FROM t;
Query OK, 0 rows affected (0.03 sec)

mysql 5.5 >
mysql 5.5 ># Profile
mysql 5.5 >
mysql 5.5 >SET PROFILING=1;
Query OK, 0 rows affected (0.00 sec)

mysql 5.5 >SELECT id FROM t WHERE id = 1;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql 5.5 >SELECT id FROM t WHERE id = 1; # Run again to use cache
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql 5.5 >SELECT id FROM v WHERE id = 1;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql 5.5 >SELECT id FROM v WHERE id = 1; # Run again to use cache
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql 5.5 >SET PROFILING=0;
Query OK, 0 rows affected (0.00 sec)

mysql 5.5 >
mysql 5.5 ># Show result
mysql 5.5 >
mysql 5.5 >SHOW PROFILES;
+----------+------------+-------------------------------+
| Query_ID | Duration   | Query                         |
+----------+------------+-------------------------------+
|        1 | 0.00044225 | SELECT id FROM t WHERE id = 1 |
|        2 | 0.00043875 | SELECT id FROM t WHERE id = 1 |
|        3 | 0.00137725 | SELECT id FROM v WHERE id = 1 |
|        4 | 0.00094700 | SELECT id FROM v WHERE id = 1 |
+----------+------------+-------------------------------+
4 rows in set (0.00 sec)

mysql 5.5 >
mysql 5.5 >SHOW PROFILE FOR QUERY 2;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000065 |
| checking permissions | 0.000013 |
| Opening tables       | 0.000031 |
| System lock          | 0.000015 |
| init                 | 0.000025 |
| optimizing           | 0.000014 |
| statistics           | 0.000074 |
| preparing            | 0.000019 |
| executing            | 0.000005 |
| Sending data         | 0.000016 |
| end                  | 0.000007 |
| query end            | 0.000006 |
| closing tables       | 0.000014 |
| freeing items        | 0.000126 |
| logging slow query   | 0.000004 |
| cleaning up          | 0.000007 |
+----------------------+----------+
16 rows in set (0.00 sec)

mysql 5.5 >SHOW PROFILE FOR QUERY 4;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000076 |
| checking permissions | 0.000015 |
| Opening tables       | 0.000484 |
| System lock          | 0.000017 |
| init                 | 0.000007 |
| checking permissions | 0.000029 |
| optimizing           | 0.000022 |
| statistics           | 0.000077 |
| preparing            | 0.000020 |
| executing            | 0.000005 |
| Sending data         | 0.000016 |
| end                  | 0.000008 |
| query end            | 0.000006 |
| closing tables       | 0.000013 |
| freeing items        | 0.000143 |
| logging slow query   | 0.000004 |
| cleaning up          | 0.000007 |
+----------------------+----------+
17 rows in set (0.00 sec)

mysql 5.5 >

Testcase.  Change the insert to t1 and it will be 7x faster.

delimiter $
drop table if exists t1,v1;
create table t1(a int not null)engine=myisam $
create or replace view v1 as select * from t1;
drop procedure if exists p1 $
create procedure p1(i int)
begin
 declare a int default 0;
 repeat   
     insert into v1 values (i);
	 set a:=a+1;
  until a > i end repeat ;

end $

delimiter ;
call p1(100000) ;