optimization for select of select in mysql query

Question

My mysql query is taking nearly 7 seconds to fetch data, where I used select of select.

SELECT s.id
     , s.user_id
     , COUNT(s.od_status) od_status
     , (SELECT count(t2.od_status) 
          FROM subscription t2 
         WHERE od_status <> 3 
           AND t2.od_status <> 7 
           AND t2.od_status <> 8 
           AND t2.user_id = s.user_id 
           AND DATE(IF(t2.rescheduling_delivery_date IS NULL, t2.dated, t2.rescheduling_delivery_date)) BETWEEN DATE_FORMAT('2021-07-26', '%Y-%m-01') AND '2021-07-26'
        ) od_status_count 
  FROM subscription 
  LEFT 
  JOIN users u
    ON u.id = s.user_id 
 WHERE DATE(IF(s.rescheduling_delivery_date IS NULL, s.dated, s.rescheduling_delivery_date)) BETWEEN DATE_FORMAT('2021-07-26' , '%Y-%m-01') AND '2021-07-26' 
 GROUP 
    BY s.user_id;

I need to optimize the above query, the thing I am getting 7 secs is by adding below select of select in above query

(select count(t2.od_status) from subscription t2 where od_status<>3 and t2.od_status<>7 and t2.od_status<>8 
and t2.user_id=subscription.user_id and 
date(IF(t2.rescheduling_delivery_date IS NULL,t2.dated,t2.rescheduling_delivery_date)) 
between DATE_FORMAT('2021-07-26' ,'%Y-%m-01') AND '2021-07-26') as od_status_count

any suggestion to optimize or another way to use that.

NOTE: I have given index to the user_id in subscription table

If you need performance, the first thing I would do is to rephrase the query to remove the IF() function. Second, I would remove date() function as well. — The Impaler
– The Impaler, Commented Jul 26, 2021 at 13:17
@TheImpaler Thanks will check that and any suggestions for select of select — Boopathi D
– Boopathi D, Commented Jul 26, 2021 at 13:20
Hi @DroidDev: convert corelated subquery part with INNER JOIN and replace IF() with COALESCE() or CASE for gain better performance. — Rahul Biswas
– Rahul Biswas, Commented Jul 26, 2021 at 13:22
You are LEFT JOINing a table from which you appear to select no columns. What am I missing? — user16425306
– user16425306, Commented Jul 26, 2021 at 13:35

Boopathi D · Accepted Answer · 2021-07-26 19:13:31Z

1

As @RahulBiswas suggested, I changed the subquery to LEFT JOIN and the query executed in 0.16 secs

select 
`subscription`.`id`, 
`subscription`.`user_id`, 
COUNT(subscription.od_status) as od_status ,
x.od_status_count
from `subscription` left join `users` on `users`.`id` = `subscription`.`user_id`
left join(
  select t2.user_id,count(t2.od_status)as od_status_count from subscription t2 where od_status<>3 and t2.od_status<>7 and t2.od_status<>8 
and 
date(IF(t2.rescheduling_delivery_date IS NULL,t2.dated,t2.rescheduling_delivery_date)) 
between DATE_FORMAT('2021-07-26' ,'%Y-%m-01') AND '2021-07-26'
group by t2.user_id)x
on x.user_id=subscription.user_id    
where date(IF(subscription.rescheduling_delivery_date IS NULL,subscription.dated,subscription.rescheduling_delivery_date)) 
between DATE_FORMAT('2021-07-26' ,'%Y-%m-01') AND '2021-07-26' 
group by `subscription`.`user_id`

I changed the subquery to joins as below,

left join(
select t2.user_id,count(t2.od_status)as od_status_count from subscription t2 where od_status<>3 and t2.od_status<>7 and t2.od_status<>8 
and 
date(IF(t2.rescheduling_delivery_date IS NULL,t2.dated,t2.rescheduling_delivery_date)) 
between DATE_FORMAT('2021-07-26' ,'%Y-%m-01') AND '2021-07-26'
group by t2.user_id)x
on x.user_id=subscription.user_id

answered Jul 26, 2021 at 19:13

Boopathi D

3692 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rahul Biswas Over a year ago

Hi @DroidDev Thanks for this query and really honored for mentioning my name.

The Impaler · Accepted Answer · 2021-07-26 13:27:18Z

0

For the optimizer to be able to use indexes you'll need to:

Rephrase your query. Remove the IF() function by using OR or UNION/UNION ALL.
Remove date() function and replace it by timestamp comparison instead.

If you don't do that you can still get a somewhat better performance by adding an index for the subquery. This is the simplest option, but won't really get great improvements. You can add the following index:

create index ix1 on subscription (user_id, od_status);

For further improvement in performance you can use a covering index instead:

create index ix1 on subscription (
   user_id,
   od_status,
   rescheduling_delivery_date, 
   dated, 
   rescheduling_delivery_date
);

answered Jul 26, 2021 at 13:27

The Impaler

49.3k10 gold badges50 silver badges90 bronze badges

Collectives™ on Stack Overflow

optimization for select of select in mysql query

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related