как можно оптимизировать этот запрос (n+1 и плотный_ранк после)

Question

Как я могу оптимизировать этот запрос

      WITH stats AS (SELECT a.IntegratorSalesAssociateID,
                      a.AgentName,
                      (
                          SELECT COUNT(*)
                          FROM properties AS p
                          WHERE a.IntegratorSalesAssociateID = p.IntegratorSalesAssociateID
                            AND p.TransactionType = '2'
                            AND MONTH(p.OrigListingDate) = MONTH(CURRENT_DATE)
                            AND YEAR(p.OrigListingDate) = YEAR(CURRENT_DATE)
                      ) AS properties_this_month
               FROM agents AS a)
SELECT stats.*,
       DENSE_RANK() over (ORDER BY stats.properties_this_month DESC) AS 'rank'
from stats

Я думаю, что, возможно, если я присоединюсь к двум таблицам и сгруппирую их как-нибудь, это будет намного лучше, в настоящее время он работает в течение 17,5 секунд, как ни странно, добавление density_rank вообще не влияет на производительность.

Соответствующая структура таблицы

      CREATE TABLE `agents`
(
    `IntegratorSalesAssociateID` varchar(15) COLLATE utf8mb4_unicode_ci NOT NULL,
    `AgentName`                  varchar(100) COLLATE utf8mb4_unicode_ci     DEFAULT NULL,
    `created_at`                 timestamp                              NULL DEFAULT NULL,
    `updated_at`                 timestamp                              NULL DEFAULT NULL
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci;


CREATE TABLE `properties`
(
    `id`                         bigint(20) UNSIGNED                    NOT NULL,
    `IntegratorSalesAssociateID` varchar(13) COLLATE utf8mb4_unicode_ci NOT NULL,
    `TransactionType`            tinyint(4)                             NOT NULL,
    `OrigListingDate`            date                                        DEFAULT NULL,
    `created_at`                 timestamp                              NULL DEFAULT NULL,
    `updated_at`                 timestamp                              NULL DEFAULT NULL
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci;

Answer 1

Учитывая, что DENSE_RANK() не влияет на производительность, вы хотите оптимизировать:

      SELECT a.IntegratorSalesAssociateID,
       a.AgentName,
       (SELECT COUNT(*)
        FROM properties p
        WHERE a.IntegratorSalesAssociateID = p.IntegratorSalesAssociateID AND
              p.TransactionType = '2' AND
              MONTH(p.OrigListingDate) = MONTH(CURRENT_DATE) AND
              YEAR(p.OrigListingDate) = YEAR(CURRENT_DATE)
         ) AS properties_this_month
FROM agents a;

Я бы переписал это так:

      SELECT a.IntegratorSalesAssociateID,
       a.AgentName,
       (SELECT COUNT(*)
        FROM properties p
        WHERE a.IntegratorSalesAssociateID = p.IntegratorSalesAssociateID AND
              p.TransactionType = 2 AND
              p.OrigListingDate >= CURRENT_DATE - INTERVAL (1 - DAY(CURRENT_DATE) DAY
         ) AS properties_this_month
FROM agents a;

Два изменения:

• TransactionTypeпохоже на число. Предполагая, что это так, я удалил одинарные кавычки. Не смешивайте типы данных! Конечно, если столбец является строкой, используйте одинарные кавычки.
• Я изменил логику даты, чтобы удалить все функции в столбце. Я предполагаю, что нет будущих "оригинальных" дат. В таком случае можно добавить еще одно условие на конец месяца.

Затем для этого запроса вам нужен индекс: properties(IntegratorSalesAssociateID, TransactionType, OrigListingDate). Фактически, этот индекс может работать с исходной версией данных.

Я искренне сомневаюсь, что использование явной агрегации улучшит производительность. GROUP BY- хотя и довольно мощный - часто медленнее, чем коррелированные подзапросы. И почти всегда медленнее (или, по крайней мере, не быстрее) с правильными индексами.

Answer 2

Вы можете попробовать это:

      ;WITH stats AS
(
    SELECT 
       p.IntegratorSalesAssociateID
       , COUNT(*) AS properties_this_month
    FROM properties AS p
    WHERE p.TransactionType = '2'
        AND MONTH(p.OrigListingDate) = MONTH(CURRENT_DATE)
        AND YEAR(p.OrigListingDate) = YEAR(CURRENT_DATE)
    GROUP BY p.IntegratorSalesAssociateID
)
SELECT 
   a.IntegratorSalesAssociateID
   , a.AgentName
   , COALESCE(s.properties_this_month, 0) AS properties_this_month
FROM agents AS a
LEFT JOIN stats s ON a.IntegratorSalesAssociateID = s.IntegratorSalesAssociateID