需要优化SQL查询-花费大量时间执行

Question

我们有一个下面的查询，大约需要6-8秒才能执行。记录总数: 522954条

(SELECT 
        * 
      FROM
        tbl_insights_copy
      WHERE insightscat = 21 
        AND submitedon >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY)) 
      ORDER BY submitedon DESC 
      LIMIT 5) 
      UNION
      (SELECT 
        * 
      FROM
        tbl_insights_copy 
      WHERE insightscat = 22 
        AND submitedon >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY)) 
      ORDER BY submitedon DESC 
      LIMIT 5) 
      UNION
      (SELECT 
        * 
      FROM
        tbl_insights_copy 
      WHERE insightscat = 23 
        AND submitedon >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY)) 
      ORDER BY submitedon DESC 
      LIMIT 5) 
      UNION
      (SELECT 
        * 
      FROM
        tbl_insights_copy 
      WHERE insightscat = 24 
        AND submitedon >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY)) 
      ORDER BY submitedon DESC 
      LIMIT 5)

是否有人可以帮助优化此查询，以减少执行时间。提前谢谢。

Answer 1

您在一个select和另一个select之间唯一更改的是列insightscat的筛选器值。我不确定这是否是您想要的，但是...

为此，您可以尝试使用IN指令。示例：

SELECT 
        * 
FROM
        tbl_insights_copy 
      WHERE insightscat in (20,21,22,23,24)
        AND submitedon >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY)) 
      ORDER BY submitedon DESC 

Answer 2

对于此查询：

SELECT ic.* 
FROM tbl_insights_copy ic
WHERE insightscat = 21 AND
      submitedon >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY)) 
ORDER BY submitedon DESC 
LIMIT 5

您想要一个关于tbl_insights_copy(insightscat, submittedon)的索引。

这应该适用于所有子查询。这可能是使用MySQL的最好方法。

Answer 3

    SELECT t1.*
   FROM (SELECT t.* ,ROW_NUMBER() OVER (ORDER BY insightscat) AS Row
       FROM 
       (select * from        
        tbl_insights_copy 
         WHERE insightscat in (20,21,22,23,24)
        AND submitedon >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY)) 
        ORDER BY submitedon DESC ) as t ) t1
   WHERE Row <= 5