温馨提示:本文翻译自stackoverflow.com,查看原文请点击:mysql - Optimize SQL Query
mysql

mysql - 优化SQL查询

发布于 2020-03-27 11:51:44

我正在尝试从两个表中获取记录。Spa和表spa_meta

我的SQL查询是

 SELECT spa_1.id as sid, spa_1.title, spa_1.doc_type, spa_1.inventory_id, inventory.unit_no as unit_no,spa_1.spa_status as spa_status, 

spa_meta.*

    FROM spa_meta

    JOIN (
       SELECT spa.*, (SELECT MAX(created_at) FROM spa_meta WHERE spa_meta.spa_id = spa.id) as max_date FROM spa ) 

AS spa_1 ON spa_1.max_date = spa_meta.created_at AND spa_1.id = spa_meta.spa_id

    LEFT JOIN inventory ON spa_1.inventory_id = inventory.id  

    where spa_1.spa_status LIKE "completed%"

     group by spa_meta.spa_id

该查询可按要求工作,但在10秒钟以上即可呈现几百条记录。我想对此进行优化。

表定义

SPA http://prntscr.com/oa30t6

SPA_META http://prntscr.com/oa2zq7

目的SPA表中有唯一的记录,而在SPA_META中,SPA具有相同ID的多个结果。那是spa.id = spa_meta.spa_id

我想从两个表中获取结果,并将Max(created_at)日期时间条目从spa_meta表中连同spa表行数据合并为一行。

问题:如何减少此查询的执行时间,或者是否有其他解决方案可从应创建了create_at列的元表中提取整行Max(created_at)

查看更多

查看更多

提问者
Esar-ul-haq Qasmi
被浏览
186
PeterHe 2019-07-03 23:19

尝试使用JOIN而不是子查询来获取max_date,并仔细检查索引:

    SELECT spa_1.id as sid, spa_1.title, spa_1.doc_type, spa_1.inventory_id, inventory.unit_no as unit_no,spa_1.spa_status as spa_status, 
    spa_meta.*
FROM spa_meta
INNER JOIN (
   SELECT s.spa_id, MAX(s.title) AS title, MAX(s.doc_type) AS doc_type, MAX(s.inventory_id) AS inventory_id,
      MAX(s.spa_status) AS spa_status,
      MAX(sm.created_at) as max_date 
   FROM spa s
   INNER JOIN spa_meta sm
   ON s.spa_id=sm.spa_id
   WHERE s.spa_status LIKE 'completed%'
   GROUP BY s.spa_id) AS spa_1
ON spa_1.max_date = spa_meta.created_at
AND spa_1.id = spa_meta.spa_id
LEFT JOIN inventory
ON spa_1.inventory_id = inventory.id;