mysql view super slow

Question

this is the query for Unified Medical Language System(UMLS) to find a word related to normalized word. this query result is 165MS, but if I am running VIEW of this same query it is taking 70 sec. I m new to the mysql. Please help me.

Query:

SELECT a.nwd as Normalized_Word, 
       b.str as String, 
       c.def as Defination, 
       d.sty as Semantic_type 
FROM mrxnw_eng a, mrconso b, mrdef c, mrsty d 
WHERE a.nwd = 'cold' 
     AND b.sab = 'Msh'
     AND a.cui = b.cui 
     AND a.cui = c.cui
     AND a.cui = d.cui
     AND a.lui = b.lui
     AND b.sui = a.sui
group by a.cui

View definition:

create view nString_Sementic as 
SELECT a.nwd as Normalized_Word, 
       b.str as String, 
       c.def as Defination, 
       d.sty as Semantic_type 
FROM mrxnw_eng a, mrconso b, mrdef c, mrsty d 
WHERE b.sab = 'Msh'
     AND a.cui = b.cui 
     AND a.cui = c.cui
     AND a.cui = d.cui
     AND a.lui = b.lui
     AND b.sui = a.sui
group by a.cui   

Selection from view:

 select * nString_Sementic   
 where nwd = 'phobia'

Lawford

The two selects strings are different? The one in the view statement lacks the a.nwd = 'cold'. I'm not sure how many extra results that will add, but that could highly impact performance. – Aalborg

This seems to be a MySQL "bug": mysqlperformanceblog.com/2007/08/12/… – Dizzy

Answer 1

You may be able to get better performance by specifying the VIEW ALGORITHM as MERGE. With MERGE MySQL will combine the view with your outside SELECT's WHERE statement, and then come up with an optimized execution plan.

To do this however you would have to remove the GROUP BY statement from your VIEW. As it is, a temporary table is being created of the entire view first, before being filtered by your WHERE statement.

If the MERGE algorithm cannot be used, a temporary table must be used instead. 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

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

Here is the link with more info. http://dev.mysql.com/doc/refman/8.0/en/view-algorithms.html

If you can change your view to not include the GROUP BY statement, to specify the view's algorithm the syntax is:

CREATE ALGORITHM = MERGE VIEW...

Edit: This answer was originally based on MySQL 5.0. I've updated the links to point to the current documentation, but I have not otherwise confirmed if the answer correct for versions >5.0.

Mashie

somehow adding Algorith type didn't help me.. My view is simply union of two tables- ANY kind of help welcome, googling didn't help much..View Query- CREATE VIEW view_residents AS SELECT * FROM residents UNION ALL SELECT * FROM deleted_residents – Cade

@Cade I would recommend using two separate queries and joining the values in code. That way it will use the indexes on the individual tables. – Dunc

In looking at your referenced pages I see "TEMPTABLE" as an algorithm type not "TEMPLATE" – Longfaced

@Longfaced , thanks. This answer was based on MySQL 5.0 and I assume what you mention has changed with later versions. I've edited the answer to note this. – Mashie

Answer 2

Assuming that mrxnw_eng.nwd is functionally dependent on mrxnw_eng.cui, try changing the group by clause of the view to include a.nwd - like so:

group by a.cui, a.nwd

Garling