Is there any faster way to perform group by row counts using two table in mysql?

Issue

I am trying to find the total number of outlets(nation_id wise) that exist in the orders table in a given date range.

select  o2.nation_id,count(o2.id) as outlet_count
    from  outlets o2
    where  o2.id in (
        SELECT  distinct o.outlet_id
            from  orders o
            where  order_date >= '2022-05-01 00:00:00'
              and  order_date <= '2022-06-30 23:59:59'
                    )
    group by  o2.nation_id ``` 

Now, this query gives the exact result but it takes around 3 seconds. Is there any way to perform this query faster? Probably less than 1 second.
N.B.: Outlets table contains around 25k data and orders table contains around 1.2M rows.

Solution

Avoiding IN will boost the performance and creating indices for nation_id & id columns in outlets table and outlet_id column of orders table will definitely improve the speed.

select  o2.nation_id,  count(Ord.outlet_id) as outlet_count
from  outlets o2
LEFT JOIN 
    (
    SELECT  distinct o.outlet_id
        from  orders o
        where  order_date >= '2022-05-01 00:00:00'
          and  order_date <= '2022-06-30 23:59:59'
    ) Ord ON o2.id = Ord.outlet_id
group by  o2.nation_id

Answered By – Ananth MK

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published