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

Вот мой запрос:

EXPLAIN SELECT Count(1), 
       user_id, 
       type 
FROM   (SELECT e.user_id, 
               e.type, 
               Max(r.date_time) last_seen, 
               e.date_time      event_time 
        FROM   events e 
               JOIN requests r 
                 ON e.user_id = r.user_id 
                    AND e.type IN( 3, 5, 6 ) 
        GROUP  BY e.user_id, 
                  e.date_time, 
                  e.type 
        HAVING last_seen < event_time) x 
GROUP  BY user_id, 
          type

Также вот результат EXPLAIN:

Также вот результат этого подзапроса (x) EXPLAIN:

Увидеть? Многое оптимально. Таким образом, проблема группируется здесь. Любая идея, как я могу сделать этот запрос лучше?

РЕДАКТИРОВАТЬ: Нам нужны две таблицы:

  1. requests таблица - новая строка будет вставлена ​​в него для каждого запроса пользователя. Таким образом, последний (самый большой) определяет, когда пользователь последний раз был на нашем сайте.

  2. events таблица - новая строка будет вставлена ​​в него для каждого ответа, комментария.

Мы говорим о веб-сайте Q/A. Все, что мы пытаемся сделать, это "отправить электронное письмо пользователям, которые получили новый комментарий / ответ после того, как они в последний раз были онлайн на нашем сайте".

Источник

4 ответа

Решение

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

select user_id, type, count(*)
from (select e.user_id, e.type, e.date_time, 
             (select max(r.date_time)
              from requests r
              where r.user_id = e.user_id
              ) as last_seen 
       from events e 
       where e.type  in ( 3, 5, 6 ) 
      ) er
where last_seen < date_time
group by user_id, type;

Затем я хочу быть уверен, что есть индексы на requests(user_id, date_time) а также events(type, user_id, date_time),

Источник

Вам нужен правильный индекс в вашей таблице, чтобы соответствовать как предложению WHERE, так и Order by для оптимизации.

table      index on...
events     ( type, user_id, date_time )
requests   ( user_id, date_time )

Я мог бы даже предложить небольшую корректировку запроса.
Измени свой

AND e.type IN( 3, 5, 6 )

в

WHERE e.type IN( 3, 5, 6 )

Потому что "e.Type" основан на вашей основной таблице запроса и не имеет ничего общего с фактическим JOIN к таблице запросов. Объединение должно представлять фактические столбцы, чтобы соответствовать между таблицами.

ПРЕДЛОЖЕНИЕ сообщение отредактировать на вопрос. Я мог бы предложить альтернативный вариант. Добавьте столбец в вашу пользовательскую таблицу для поля даты / времени lastRequest. Затем каждый раз, когда вводится запрос для этого пользователя, обновите поле в пользовательской таблице. Вам не нужно держать подзапрос max(), чтобы узнать когда. Это может упростить ваш запрос до чего-то вроде... По мере того, как ваша таблица запросов увеличивается, увеличивается и время запроса. Посмотрев прямо на пользовательскую таблицу ONCE для уже известного последнего запроса, вы получите свой ответ. Запрашивать 10 тыс. Пользователей или 2 млн. Запросов... ваш выбор, чтобы пахать:)

select 
      u.user_id,
      e.type,
      count(*) CountPerType,
      min( e.date_time ) firstEventDateAfterUsersLastRequest
   from
      user u
         join events e 
            on u.user_id = e.user_id
           AND e.type in ( 3, 5, 6 )
           AND e.date_time > u.lastRequest
   group by
      u.user_id,
      e.type

Таким образом, ваше присоединение уже имеет базовую дату / время для каждого пользователя, и вы можете просто искать записи, поступающие ПОСЛЕ того, как человек последний раз что-то запрашивал (следовательно, последующие действия).

Затем, чтобы подготовить новый столбец в вашей пользовательской таблице, вы можете просто обновить с max( request.date_time) для каждого пользователя.

Если человек активен по состоянию на: 27 ноября, и есть 5 ответов на 3 различных типа событий ПОСЛЕ того, что вы все равно получите этого человека на дату его 27 ноября, но у других людей могут быть более новые или более старые даты "latestRequest".

Просто необязательная мысль..

Источник

http://sqlfiddle.com/

ALTER TABLE `events` ADD INDEX e_type (type);
ALTER TABLE `events` ADD INDEX user_time (user_id, date_time);
ALTER TABLE requests ADD INDEX user_time (user_id, date_time);

SELECT  COUNT(*),
        e.user_id, 
        e.type
FROM `events` e 
JOIN  (
  SELECT user_id, Max(r.date_time) last_seen
  FROM requests r 
  GROUP BY user_id
) r
ON e.user_id = r.user_id 
   AND e.date_time > r.last_seen
WHERE e.type IN( 3, 5, 6 ) 
GROUP  BY e.user_id,  
       e.type
Источник

Посмотрите, получит ли это "правильные" ответы:

SELECT  COUNT(DISTINCT(e.date_time),
        e.user_id, e.type
    FROM  events e
    JOIN  requests r  ON  e.user_id = r.user_id
                     AND  e.type IN( 3, 5, 6 )
    GROUP BY  e.user_id, e.type
    HAVING  MAX(r.date_time) < e.event_time

Индексы:

e:  INDEX(type)   -- may be useful (depends on cardinality)
r:  INDEX(user_id, date_time)  -- in this order
Источник
Другие вопросы по тегам