我正在尝试从两个表中获取记录。表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_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)
?
尝试使用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;
感谢@PeterHe,感谢您的宝贵时间,我在结束时提出了另一个查询,它运行正常。