Optimize SQL Query

发布于 2020-03-27 10:28:26

I am trying to fetch records from two tables. Table Spa and Table spa_meta.

My Sql Query is

 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

This query works as required but renders few hundred records in more than 10 seconds. I want to optimize this.

Table Definations

SPA http://prntscr.com/oa30t6

SPA_META http://prntscr.com/oa2zq7

Aim: There are unique records in SPA table and in SPA_META there are several results for same ID in SPA. That is spa.id = spa_meta.spa_id.

I want to fetch the results from both tables with Max(created_at) date time entry from spa_meta table along with spa table row data in one row combined.

Question : How to reduce the execution time for this query or is there any other solution to fetch the entire row from meta table where created_at column should be Max(created_at)?

Questioner

Esar-ul-haq Qasmi

Viewed

101

Chinese

Original

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;

Optimize SQL Query

Related issues