[Solved] MySQL view built from simple views (algorithm = merge) still has performance issue

EverSQL Database Performance Knowledge Base

MySQL view built from simple views (algorithm = merge) still has performance issue

Database type:

I define several basic views (Simple view with only left join operation). And then define another simple view (let's naming it as inherited_view) from those basic views. The view algorithm is merge. Simple select from the inherited_view performs terrible.

But I cannot find derived table from 'explain' output. Also I cannot understand why the performance is so bad.

Here is the explain result: view on views

Then I try to rewrite the inherited_view from original tables directly. Then it performs much better. But the 'explain' result doesnt change too much. I am not sure why the performance improves.

Here is the explain result:

view on tables

So my question is why the performance is bad even though all views are created with algorithm = merge. Thanks.

update view definition:

basic views are just select columns from several tables:

CREATE VIEW basic_View AS
SELECT column1, column2,....
FROM (((((table1 
LEFT JOIN table2 ON table2.xid = table1.id 
LEFT JOIN table3 ON table3.yid = table1.id
  ...
where ('string1' = tablex.column1) and (value = tabley.column2);


CREATE VIEW inherited_view AS
SELECT column1, column2....
FROM basic_view1
LEFT JOIN basic_view2 on ((basic_view2.result_id = basic_view1.id and basic_view2.name = 'string1'))
LEFT JOIN basic_view2 on ((basic_view2.result_id = basic_view1.id and basic_view2.name = 'string2'))
LEFT JOIN basic_view2 on ((basic_view2.result_id = basic_view1.id and basic_view2.name = 'string3'))

The record number is 20 thousands. No cache as time keeps unchanged for both case no matter how many time I run.

The query statement to test the performance is:

SELECT * FROM inherited_view WHERE name1 = 'str1' and name2 = 'str2';

The real performance data is: (I run the fast one firstly. No cache I think. The data keeps consistent.)

35 rows in set (21 sec) for view on views

35 rows in set (0.7 sec) for view on tables


Possible root cause: The magic is in the last three LEFT JOIN or you can check the last three rows in the explain result.

CREATE VIEW inherited_view AS
SELECT column1, column2....
FROM basic_view1
LEFT JOIN basic_view2 on ((basic_view2.result_id = basic_view1.id and basic_view2.name = 'string1'))
LEFT JOIN basic_view2 on ((basic_view2.result_id = basic_view1.id and basic_view2.name = 'string2'))
LEFT JOIN basic_view2 on ((basic_view2.result_id = basic_view1.id and basic_view2.name = 'string3'))

basic_view2 is a simple view, the data looks like:

result_id    name     value
1            owner    Tom
1            status   Fail
2            owner    Jerry
2            status   Pass

From the last three rows of the explain results, you can see there are two possible_keys, first one is index of column 'result_id', and the second one is index of column 'name'.

Query of view on table, uses the first index ('result_id').

But query of view on view, uses the second index ('name').

After I drop the index of 'name', both view on view performs as well as view on table.

Any one can tell me what exactly happens? Thanks.

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid Selecting Unnecessary Columns (query line: 2): Avoid selecting all columns with the '*' wildcard, unless you intend to use them all. Selecting redundant columns may result in unnecessary performance degradation.
  2. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
ALTER TABLE `inherited_view` ADD INDEX `inherited_view_idx_name1_name2` (`name1`,`name2`);
The optimized query:
SELECT
        * 
    FROM
        inherited_view 
    WHERE
        inherited_view.name1 = 'str1' 
        AND inherited_view.name2 = 'str2'

Related Articles



* original question posted on StackOverflow here.