Блог

У меня есть запрос, который я использую в своей модели CodeIgniter для получения количества списков товаров между определенными днями. Это прекрасно работает, когда в моей таблице меньше элементов, но в моей таблице более 100 000 записей, и чтобы просто получить результат за 2 дня, требуется около 3-4 минут. Чем дольше дни » от » и » до » отделены друг от друга, тем больше времени это занимает.

Вот запрос: (Dbfiddle:https://dbfiddle.uk/?субд=mysql_8.0amp;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;
 

По сути, этот запрос выполняет status_from из журналов, где дата указана во время и после выбранного диапазона дат, и извлекает added_date из списков, где дата совпадает с диапазоном дат, выбранным пользователем, и вычисляет его. Как только он извлек эти записи, он проверяет таблицу на предмет того, какую переменную содержит этот статус, и выполняет a sum(case when else 0) , чтобы получить общее количество.

Одна из причин, по которой я думаю, что запрос медленный, заключается в том, что он должен вычислять сумму для статусов в самом запросе, поэтому, возможно, было бы быстрее выполнить подсчет на стороне php? Если да, то как я могу создать инструкцию для итерации подсчета в моем классе представления.

Текущий Класс Представления:

 <?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 }  ?>
 

Или, если возможно, если бы был какой-либо способ получить тот же вывод этого запроса, но более быстрым способом.

Это быстрее? Если вы вызываете запрос чаще, вы можете рассмотреть возможность сохранения ROW_NUMBER в logs таблицу

 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;
 

Я упростил ваш запрос, но я не уверен, что вы получите значительное улучшение во времени выполнения. Необходимо определить подходящие индексы.

Пожалуйста, внимательно проверьте его и убедитесь, что он правильно выведен.

 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;