使用UNION关键字的查询在mysql中运行速度非常慢

Why is it that a query with UNION keyword runs very slow in mysql?

为什么使用UNION关键字的查询在mysql中运行速度非常慢?

I have created a query using UNION keyword but everytime I execute it, it takes a long time before the output was displayed.

我使用UNION关键字创建了一个查询,但每次执行它时,都需要很长时间才能显示输出。

This is the query I used.

这是我使用的查询。

SELECT process_trained AS Training_Title, 
   org_party AS Organizing_Party, 
   duration AS Duration, 
   Concat(Date_format(sptrain_from, '%d-%b-%y'), " to ", 
   Date_format(sptrain_to, '%d-%b-%y'), "") AS SpecialTraining_Date, 
   trainor AS Trainor, 
   category AS Category, 
   training_type AS Training_Type, 
   train_id, 
   Date_format(cert_date, '%d-%b-%Y') AS Date_Certified, 
   Date_format(re_certdate, '%d-%b-%Y') AS Re_Certificaton, 
   remarks, 
   filename 
FROM(SELECT *, 
               cert_date AS cdate 
        FROM   tbldirtraining 
        UNION ALL 
        SELECT *, 
               sptrain_from 
        FROM   tbldirtraining) jes 
WHERE  emp_id = '6555' 
       AND cdate <> '' 
ORDER  BY cdate; 

Is there any possible way that can help on how to run this query faster.

是否有任何可能的方法可以帮助您更快地运行此查询。

Thanks in advance.

提前致谢。

1 个解决方案

#1

Try this

     SELECT process_trained AS Training_Title, 
 org_party AS Organizing_Party, 
 duration AS Duration, 
 Concat(Date_format(sptrain_from, '%d-%b-%y'), " to ", 
 Date_format(sptrain_to, '%d-%b-%y'), "") AS SpecialTraining_Date, 
 trainor AS Trainor, 
 category AS Category, 
 training_type AS Training_Type, 
 train_id, 
 Date_format(cert_date, '%d-%b-%Y') AS Date_Certified, 
 Date_format(re_certdate, '%d-%b-%Y') AS Re_Certificaton, 
 remarks, 
 filename 
FROM  tbldirtraining 
WHERE  emp_id = '6555' 
AND cert_date <> '' 
ORDER  BY cert_date desc,sptrain_from asc;