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'

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.