Converting subquery with SUM and many WHEREs to Join structure for performance-CodePudding

Can I obtain the same result in a more efficient way?

Below query is fast for few records, but I have a huge performance problem with more than 100 results and already large tasks table.

I wanted to redo it to a JOIN struct, but with no luck so far.

Any help would be appreciated.

SELECT 
    `t`.*,
    (
        SELECT SUM(`tasks_sub`.`delivered`) 
        FROM `tasks` AS `tasks_sub`
        WHERE ((`tasks_sub`.`table` = `t`.`table` AND `tasks_sub`.`proces_id` = `t`.`proces_id`)
            OR (`tasks_sub`.`order_id` = `t`.`order_id`))
            AND `tasks_sub`.`index` = `t`.`index`
    ) AS `sum_delivered`
FROM 
    `tasks` AS `t`

Table tasks structure:

Tasks
`id`, `table`, `proces_id`, `index`, `delivered`

CodePudding user response：

Consider this rewrite

SELECT t.*,
    (   SELECT  SUM(ts.`delivered`)
            FROM  `tasks` AS ts
            WHERE  ts.`table` = `t`.`table`
              AND  ts.`proces_id` = `t`.`proces_id`
              AND  ts.`index` = `t`.`index` 
    )  
    (   SELECT  SUM(ts.`delivered`)
            FROM  `tasks` AS ts
            WHERE  ts.`order_id` = `t`.`order_id`
              AND  ts.`index` = `t`.`index` 
    ) AS delivered
FROM tasks AS t

Together with two new indexes:

tasks:  INDEX(table, proces_id, index,  delivered)
tasks:  INDEX(order_id, index,  delivered)