Bug #5969 Performance Problem with updatable VIEW
Submitted: 8 Oct 2004 5:49 Modified: 10 Oct 2004 10:52
Reporter: Georg Richter Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.2 OS:Linux (Linux)
Assigned to: Oleksandr Byelkin CPU Architecture:Any

[8 Oct 2004 5:49] Georg Richter 
Description:
There is a performance bottleneck with updatable VIEWS. A simple UPDATE on a VIEW is x0000 times slower than updating the underlying table. 

(In our test environment with a larger table than in how-to-repeat, UPDATE VIEW took 86 seconds, UPDATE TABLE < 0.1 sec.).

How to repeat:
-- create table and view
CREATE TABLE a1 (a varchar(35), b int, primary key(a,b)) engine=Innodb;
CREATE VIEW v_a1 AS SELECT a,b FROM a1;

-- insert one known record
INSERT INTO a1 (a,b) VALUES ('thisistheonlyknownkey', 23);

\d |

-- procedure for adding garbage
CREATE PROCEDURE insert_stuff()
BEGIN
  DECLARE i INT DEFAULT 500000;
  WHILE i > 0 DO
    INSERT INTO a1 VALUES (MD5(UUID()), CAST( RAND() * 10 AS SIGNED));
    SET i=i-1;
  END WHILE;
END |

\d ;

-- insert garbage
SET AUTOCOMMIT=0;
CALL insert_stuff();
COMMIT;

Here is the output from UPDATE commands:

[07:31] root@test> UPDATE v_a1 SET b=22 WHERE a='thisistheonlyknownkey' AND b=23;
Query OK, 1 row affected (6.61 sec)
Rows matched: 1  Changed: 1  Warnings: 0

5.0.2-alpha-debug-log
[07:31] root@test> UPDATE a1 SET b=21 WHERE a='thisistheonlyknownkey' AND b=22;
Query OK, 1 row affected (0.07 sec)
Rows matched: 1  Changed: 1  Warnings: 0
[10 Oct 2004 8:03] Oleksandr Byelkin 
ChangeSet 
  1.1645 04/10/10 11:01:05 bell@sanja.is.com.ua +3 -0 
  prevent substituting Item_ref as VIEW fields in WHERE conditions (BUG#5969) 
  prevent filling temporary tables of views on list fields command
[10 Oct 2004 10:52] Oleksandr Byelkin 
Thank you for bugreport! Bug is fixed now and patch is pushed into source repository.