Mysql query performing very slowly-CodePudding

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.

CodePudding user response：

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.

CodePudding user response：

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;