如何通过查询优化在MySQL中获得组合结果? [英] How to get combined result in MySQL with query optimization?

问题描述

我有桌子，

id  | OpenDate             |  CloseDate
------------------------------------------------
1   | 2013-01-16 07:30:48  |  2013-01-16 10:49:48
2   | 2013-01-16 08:30:00  |  NULL

我需要获得如下所示的综合结果

I needed to get combined result as below

 id | date                 |   type
 ---------------------------------
 1  | 2013-01-16 07:30:48  |  Open
 1  | 2013-01-16 10:49:48  |  Close
 2  | 2013-01-16 08:30:00  |  Open

我使用UNION获得了以上输出(如果没有UNION，我们可以实现吗?)

I used UNION to get above output (can we achieve without UNION?)

SELECT id,date,type FROM(
   SELECT id,`OpenDate` as date, 'Open' as 'type' FROM my_table
  UNION ALL
   SELECT id,`CloseDate` as date, 'Close' as 'type' FROM my_table
                       )AS `tab` LIMIT 0,15

我得到了期望的输出，但是现在在性能方面->我的表中有4000条记录，并通过UNION对其进行合并并提供了大约8000条记录，这使得加载该站点非常缓慢(超过13条)秒).如何优化此查询以固定输出?

I am getting the desired output, but now in performance side--> i have 4000 records in my table and by doing UNION its combining and giving around 8000 records, which is making very slow to load the site(more than 13 sec). How can i optimize this query to fasten the output?

我也在子查询中尝试了LIMIT，如果我在子查询中使用LIMIT，则分页偏移不能正常工作.请帮我解决这个问题.

I tried LIMIT in sub-query also, pagination offset is not working properly as it should if i use LIMIT in sub-query. Please help me to resolve this.

更新

解释结果

id   select_type  table     type   key    key_len    ref     rows   Extra
1    PRIMARY     <derived2> ALL   NULL      NULL      NULL   8858    
2    DERIVED     orders     index OpenDate   4        NULL   4588   Using index
3    UNION       orders     index CloseDate  4        NULL   4588   Using index
NULL UNION RESULT<union2,3> ALL   NULL      NULL      NULL   NULL    

推荐答案

我将执行以下操作:

SELECT
  id,
  IF(act, t1.OpenDate, t1.CloseDate) as `date`,
  IF(act, 'Open', 'Close') as `type`
FROM my_table t1
JOIN (SELECT 1 as act UNION SELECT 0) as _
JOIN my_table t2 USING (id);

这篇关于如何通过查询优化在MySQL中获得组合结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！