Mysql query performing very slowly

I have a query I'm using in my CodeIgniter model to fetch the count of listings of products between particular days. This works fine when there are less items in my table, but there are more than 100,000 entries in my table and to just get the output of 2 days it takes around 3-4 minutes. The longer the from and to days are apart, the more time it takes.

Here is the query: (Dbfiddle:https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=e7a99f08ecd217cbeb09fe6676cfe645)

with Y as (
  with recursive D (n, day) as (
    select 1 as n, '2021-09-25' my_date
    union
    select n+1, day + interval 1 day from D
      where day + interval 1 day < '2021-10-15'
  ) select * from D
), X as (
  select Y.day,
         l.*,
         (select status_from from logs
            where logs.refno = l.refno
              and logs.logtime >= Y.day
            order by logs.logtime
            limit 1) logstat
    from listings l, Y
    where l.added_date <= Y.day
), Z as (
  select X.day, ifnull(X.logstat,X.status) stat_day, count(*) cnt
    from X
    group by X.day, stat_day
)
select Z.day,
  sum(case when Z.stat_day = 'D' then Z.cnt else 0 end ) Draft,
  sum(case when Z.stat_day = 'A' then Z.cnt else 0 end ) Action,
  sum(case when Z.stat_day = 'Y' then Z.cnt else 0 end ) Publish,
  sum(case when Z.stat_day = 'S' then Z.cnt else 0 end ) Sold,
  sum(case when Z.stat_day = 'L' then Z.cnt else 0 end ) Let
  from Z
  group by Z.day
  order by Z.day;

Basically what this query is doing is status_from from logs where the date is during and after the selected date range and taking added_date from listings where the date falls before the from date range picked by the user and calculates it. Once it has retrieved those records, it checks the table for what variable that status holds and does a sum(case when else 0) to get the total count.

One reason I'm thinking the query is slow is because it has to compute the sum for the statuses in the query itself, so maybe it would be faster to do the count part in the php side? If so then how can I create a statement for it to iterate the count in my view class.

Current View Class:

<?php
            foreach($data_total as $row ){
               $draft = $row->draft ? $row->draft : 0;
               $publish = $row->publish ? $row->publish : 0;
               $action = $row->action ? $row->action : 0;
               $sold = $row->sold ? $row->sold : 0;
               $let = $row->let ? $row->let : 0;                              
          ?>
              <tr>
                    <td><?= $row->day?></td>
                    <td><?= $draft ?></td>
                    <td><?= $publish ?></td>
                    <td><?= $action ?></td>
                    <td><?= $sold ?></td>
                    <td><?= $let ?></td>
              </tr>
          <?php }  ?>

Or if possible if would there be any way to get the same output of this query but in a faster way.

I simplified your query, but I'm not sure you'll get a significant improvement in time execution. You must define suitable indexes.

Please check it carefully and make sure its output is right.

WITH RECURSIVE 
  cal AS (SELECT '2021-09-25' AS day
    
          UNION ALL
    
          SELECT day + interval 1 day 
          FROM cal
          WHERE day + interval 1 day < '2021-10-15'),
  
  X AS (SELECT DISTINCT
                  cal.day,
                  l.id,
                  l.status,
                  FIRST_VALUE(status_from) OVER (PARTITION BY logs.refno, cal.day ORDER BY logs.logtime) AS logstat
        FROM listings l
        INNER JOIN cal ON l.added_date <= cal.day
        LEFT JOIN logs ON logs.refno = l.refno AND logs.logtime >= cal.day)

SELECT X.day,
       COUNT(CASE WHEN IFNULL(X.logstat, X.status) = 'D' THEN 1 END) Draft,
       COUNT(CASE WHEN IFNULL(X.logstat, X.status) = 'A' THEN 1 END) Action,
       COUNT(CASE WHEN IFNULL(X.logstat, X.status) = 'Y' THEN 1 END) Publish,
       COUNT(CASE WHEN IFNULL(X.logstat, X.status) = 'S' THEN 1 END) Sold,
       COUNT(CASE WHEN IFNULL(X.logstat, X.status) = 'L' THEN 1 END) Let
FROM X
GROUP BY X.day
ORDER BY X.day;