Bug #23136 Views performance

• Submitted: 10 Oct 2006 12:17
• Modified: 11 Oct 2006 6:31
• Reporter: Nuno Soares
• Status: Not a Bug
• Category: MySQL Server: Views
• Severity: S1 (Critical)
• Version: 5.0.19
• OS: Linux (Linux)
• CPU Architecture: Any
• Tags: performance, Views

[10 Oct 2006 12:17] Nuno Soares

Description:
Description:
Views have slow performance on tables with large amount of data (my base table
has 30000 rows and it's growing very fast).

create view v1 as
select c1, c2, c3, sum(c4) as total
  from t1
 group by c1, c2, c3

when i run the query
select * from v1 where c1 = 1
the execution time is 0.4500s

and

select c1, c2, c3, sum(c4) as total
  from t1
 where c1 = 1
 group by c1, c2, c3
the execution time is 0.0032s

what can i do to improve the view execution time ?

How to repeat:
select * from v1 where c1 = 1

select c1, c2, c3, sum(c4) as total
  from t1
 where c1 = 1

[10 Oct 2006 13:38] Valeriy Kravchuk

Thank you for a problem report. Please, try to redefine your view with ALGORITHM=MERGE (read http://dev.mysql.com/doc/refman/5.0/en/create-view.html for the details), check perfromance again and inform about the results.

[10 Oct 2006 13:40] Valeriy Kravchuk

Bug #23135 was marked as a duplicate of this one.

[10 Oct 2006 14:27] Nuno Soares

It's doesn't alow me to create with MERGE only with TEMPTABLE.

[11 Oct 2006 6:31] Valeriy Kravchuk

Sorry, but this is expected and documented behaviour for views like yours:

create view v1 as
select c1, c2, c3, sum(c4) as total
  from t1
 group by c1, c2, c3

that is, those having "GROUP BY" clause. It is documented at http://dev.mysql.com/doc/refman/5.0/en/create-view.html:

"The MERGE algorithm requires a one-to-one relationship between the rows in the view and the rows in the underlying table. If this relationship does not hold, a temporary table must be used instead. Lack of a one-to-one relationship occurs if the view contains any of a number of constructs:
    - Aggregate functions (SUM(), MIN(), MAX(), COUNT(), and so forth)
    - DISTINCT
    - GROUP BY
    - HAVING
    - UNION or UNION ALL
    - Refers only to literal values (in this case, there is no underlying table)"

So, it is not possible now to make queries to this view run faster.