MySql performance query vs view with 'explain' output

Question

I'm trying to understand why a direct query takes ~0.5s to run but a view using the same query takes ~10s to run. MySql v5.6.27.

Direct Query:

select 
    a,b, 
    (select count(*) from TableA i3 where i3.b = i.a) as e,
    func1(a) as f, func2(a) as g
from TableA i
where i.b = -1 and i.a > 1500;

Direct Query 'explain' Results:

id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra
1,PRIMARY,i,range,PRIMARY,PRIMARY,4,\N,3629,Using where
2,DEPENDENT SUBQUERY,i3,ALL,\N,\N,\N,\N,7259,Using where

The View's definition/query is the same without the 'where' clause...

select 
    a,b,
    (select count(*) from TableA i3 where i3.b = i.a) as e,
    func1(a) as f, func2(a) as g
from TableA i;

Query against the view:

select * from ViewA t where t.b = -1 and t.a > 1500;

Query of View 'explain' results:

id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra
1,PRIMARY,<derived2>,ALL,\N,\N,\N,\N,7259,Using where
2,DERIVED,i,ALL,\N,\N,\N,\N,7259,\N
3,DEPENDENT SUBQUERY,i3,ALL,\N,\N,\N,\N,7259,Using where

Why does the query against the view end up performing 3 full table scans whereas the direct query performs ~1.5?

Answer 1

The short answer is: the MySQL optimizer is not clever enough to do it.

When processing a view, MySQL can either merge the view or create a temporary table for it:

• For MERGE, the text of a statement that refers to the view and the view definition are merged such that parts of the view definition replace corresponding parts of the statement.

• For TEMPTABLE, the results from the view are retrieved into a temporary table, which then is used to execute the statement.

This applies in a very similar way to derived tables and subqueries too.

The behaviour you are seeking is the merge. This is the default value, and MySQL will try to use it whenever possible. If it is not possible (or rather: if MySQL thinks it is not possible), MySQL has to evaluate the complete view, no matter if you only need one row out of it. This obviously takes more time, and is what happens in your view.

There is a list of things that prevent MySQL from using the merge algorithm:

MERGE cannot be used if the view contains any of the following constructs:

• Aggregate functions (SUM(), MIN(), MAX(), COUNT(), and so forth)

• DISTINCT

• GROUP BY

• HAVING

• LIMIT

• UNION or UNION ALL

• Subquery in the select list

• Assignment to user variables

• Refers only to literal values (in this case, there is no underlying table)

You can test this if MySQL will merge or not: try to create the view specifying the merge-algorithm:

create algorithm=merge view viewA as ...

If MySQL doesn't think it can merge the view, you get the warning

1 warning(s): 1354 View merge algorithm can't be used here for now (assumed undefined algorithm)

In your case, the Subquery in the select list is preventing the merge. This is not because it would be impossible to do. You have already prooven that it is possible to merge it: by just rewriting it.

But the MySQL optimizer didn't see that possibility. It is not specific to views: it will actually not merge it either if you use the unmerged viewcode directly: explain select * from (select a, b, ... from TableA i) as ViewA where .... You would have to test this on MySQL 5.7, as MySQL 5.6 will not merge in this situation on principle (as, in a query, it assumes you want to have a temptable here, even for very simple derived tables that could be merged). MySQL 5.7 will by default try to do it, although it won't work with your view.

As the optimizer gets improved, in some situation, the optimizer will merge even in cases where there is an subquery in the select list, so there are some exceptions to that list. MariaDB, which is based on MySQL, is actually a lot better doing the merge optimization, and would merge your view just like you did it - so it is possible to do it even as a machine.

So to summarize: the MySQL optimizer is currently not clever enough to do it. And you unfortunately cannot do much about it, except testing if MySQL accepts algorithm=merge and then not using views that MySQL cannot merge and instead merge them yourself.