Mysql query performing very slowly

Question

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.

Answer 1

If your final output is going to be on a website, a snapshot of data is normally better practice than a live feed for Past Activities. I have used in the past a stored procedures to update a table daily with Past Activites, then use a view to Select Past_Activities unioned to Current_Activities to decrease load time for my viewers.

Answer 2

Is this faster? If you are calling query more frequently you can consider save ROW_NUMBER to logs table

with calendar as (
with recursive cal (n, day) as (
    select 1 as n, '2021-09-25' my_date
    union
    select n+1, day + interval 1 day from cal
    where day + interval 1 day < '2021-10-15'
    )select * from cal
), loggs as (
    select
         ROW_NUMBER() OVER (partition by refno order by logtime) as RN
        ,status_from as logstat
        ,refno
        ,logtime
    from logs
),X as (
  select cal.day,
         l.*,
         logs.logstat,
         RN,
         min(RN) over (partition by l.refno, cal.day) as RN_MIN
    from listings l
    join calendar as cal on l.added_date <= cal.day
    left join loggs as logs on logs.refno = l.refno and logs.logtime >= cal.day
), Z as (
  select X.day, ifnull(X.logstat,X.status) stat_day, count(*) cnt
    from X
    where ifnull(RN, 0) = ifnull(RN_min, 0)
    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;

Answer 3

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;