Select subquery slow down the execution how to fix it-CodePudding

 SELECT
  (SELECT SUM(`m_amount`) FROM `tr10_project_payment` WHERE `m_project_id`=`plan_booking`.`plan_book_id`) AS Total_deposit,
   (CASE WHEN (`plan_booking`.`plan_type`=2 || `plan_booking`.`plan_type`=4) THEN (CASE WHEN DATE_ADD((SELECT GetMaturityDate(`plan_booking`.`plan_book_date`,`plan_booking`.`total_paid_ins`,`plan_booking`.`plan_mode`)),INTERVAL 90 DAY)<NOW() THEN '1' ELSE '2' END) ELSE '2' END) AS `Installmen`
  FROM
  `plan_booking`

tr10_project_payment table has 400000 records. This whole query will take approx 35s to execute any Anyone know how can i fix the performance issue.

CodePudding user response：

try the query below

SELECT SUM(`m_amount`) AS Total_deposit,
  (CASE 
    WHEN (`plan_booking`.`plan_type`=2 || `plan_booking`.`plan_type`=4) 
    THEN (
        CASE 
            WHEN DATE_ADD((
                SELECT GetMaturityDate(`plan_booking`.`plan_book_date`,`plan_booking`.`total_paid_ins`,`plan_booking`.`plan_mode`)),
                INTERVAL 90 DAY)<NOW() THEN '1' ELSE '2' END) 
                ELSE '2' END) AS `Installmen`
    FROM `tr10_project_payment`
    JOIN `plan_booking` ON `plan_booking`.`plan_book_id` = `tr10_project_payment`.`m_project_id`

CodePudding user response：

Some rewrites:

SELECT  ( SELECT  SUM(tpp.`m_amount`)
            FROM  `tr10_project_payment` AS tpp
            WHERE  tpp.`m_project_id` = pb.`plan_book_id` 
        ) AS Total_deposit,
        IF ( pb.`plan_type` IN (2, 4),
             IF ( GetMaturityDate(pb.`plan_book_date`,
                                  pb.`total_paid_ins`,
                                  pb.`plan_mode`) <
                            NOW() - INTERVAL 90 DAY,
                  1, 2),
             2)
        ) AS `Installmen`
    FROM  `plan_booking` AS pb

And some indexes:

tpp:  INDEX(m_project_id,  m_amount) -- unless m_project_id is the PK
pb:  INDEX(plan_book_id)  -- unless it is already the PRIMARY KEY

But that still requires scanning all of pb, plus reaching into tpp repeatedly. And it depends on a function (GetMaturityDate) that can't be optimized away.

This reformulation may work faster since it does a full scan of tpp to get all the Total_deposits:

SELECT  td.Total_deposit,
        IF ( pb.`plan_type`=2 || pb.`plan_type`=4),
             IF ( GetMaturityDate(pb.`plan_book_date`,
                                  pb.`total_paid_ins`,
                                  pb.`plan_mode`) <
                            NOW() - INTERVAL 90 DAY,
                  1, 2),
             2)
        ) AS `Installmen`
    FROM ( SELECT  tpp.m_project_id,
                   SUM(tpp.`m_amount`) AS Total_deposit
            FROM  `tr10_project_payment` AS tpp
            GROUP BY tpp.m_project_id
         ) AS td 
    JOIN `plan_booking` AS pb  ON td.m_project_id = pb.`plan_book_id`

(The same indexes are recommended.)

About how many rows in each table? This may lead to yet another formulation.