CODEWITHSUNDEEP
mysql
Esar-ul-haq Qasmi
Esar-ul-haq Qasmi

Reputation: 1084

Optimize SQL Query - Fetch records from two tables with multiple rows in second table

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)?

Upvotes: 2

Views: 88

Answers (1)

PeterHe
PeterHe

Reputation: 2766

Try to use JOIN instead of the subquery to get the max_date, and double check indexes:

    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;

Upvotes: 1

Related Questions