Mysql view super slow

Question

this is a query for the Unified Medical Language System (UMLS) to find a word related to a normalized word. this query result is 165MS, but if I run VIEW of the same query, it takes 70 seconds. I am new to 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 

Select from view:

  select * nString_Sementic where nwd = 'phobia' 

Answer 1

You can get better performance by specifying ALEWGERIMM VIEW as MERGE. With MERGE, MySQL will combine the view with its external SELECT WHERE statement, and then come up with an optimized execution plan.

To do this, you will need to remove the GROUP BY statement from your VIEW. However, if the GROUP BY clause is included in your view, MySQL will choose the TEMPLATE algorithm. First, a temporary table is created for the entire view, before being filtered using the WHERE clause.

If the MERGE algorithm cannot be used, you must use a temporary table instead. MERGE cannot be used if the view contains any of the following constructs:

Aggregate functions (SUM (), MIN (), MAX (), COUNT (), etc.)

Distinct

GROUP BY

HAVING

LIMIT

UNION or UNION ALL

Subquery in the selection list

Applies only to literal values ​​(in this case there is no underlying table)

Here is a link with more information. http://dev.mysql.com/doc/refman/5.0/en/view-algorithms.html

If you can change your view to not include the GROUP BY clause, the syntax for specifying the presentation algorithm is:

 CREATE ALGORITHM = MERGE VIEW... 

Answer 2

Assuming mrxnw_eng.nwd functionally dependent on mrxnw_eng.cui , try changing the group as suggested by the view to include a.nwd - like this:

 group by a.cui, a.nwd