Как вывести последние действия юзеров на сайте?

Question

[Радость моя]

Есть несколько пользователей, по которым надо вытащить их последние действия на сайте.

Структура таблиц пользователей:

id, firstname
---------------
1, Vasiliy
2, Alex
...

Структура таблицы действий:

id, user_id, action_name, dttm
-----------------------------------
336, 2, SEARCH, 2019-10-04 12:00:01
335, 2, CREATE_POST, 2019-10-04 11:20:44
334, 1, LOGIN, 2019-10-04 11:18:55
...

т.е. в данном случае у user = 2 последнее действие - поиск (SEARCH), а у user = 1 последнее действие вход (LOGIN)

как мне вывести это в таком виде, для нескольких пользователей (не для всех):

user_id, last_action, last_action_date:
----------------------------------
1, LOGIN, 2019-10-04 11:18:55
2, SEARCH, 2019-10-04 12:00:01

Таблица с логами действий крупная (более млн записей), и мои joinы слишком долго работают. Задача вроде тривиальная, но моя кукуха меня подводит.

Спасибо за помощь!

Answer 1

[netyshka]

Одни джойны)))

SELECT a.user_id, ab.last_action, s.times
FROM user a
INNER JOIN (SELECT user_id, max(last_action_date) AS times
FROM user
GROUP BY user_id) s ON s.user_id = a.user_id and s.times = a.last_action_date
Left join user ab on s.user_id = ab.user_id and ab.last_action_date = s.times and a.last_action = ab.last_action
Where a.user_id in (1,2)
order by null

Comments

[Радость моя]

не пойму где в запросе таблица с действиями :)

[netyshka]

Радость моя, ab - последний джойн

[netyshka]

Радость моя, ----> мау

[netyshka]

Радость моя, а если набор юзеров упорядоченный, вообще лучше сделать where a.user_id >=1 and a.user_id <=2

[Радость моя]

спасибо, невероятная скорость! (и запроса и вашей помощи)

[netyshka]

Радость моя, пожалуйста)

[Евгений Самсонов]

Радость моя, Мне теперь интересно, как это работает быстрее, моего варианта. Можно EXPLAIN? :)

[netyshka]

Евгений Самсонов, :))))

[Радость моя]

Евгений Самсонов, это не запрос, это ракета какая-то

[Евгений Самсонов]

Радость моя, да, я тоже у себя посмотрел. На вид это очень плохо. Я в недоумении :)

[netyshka]

Евгений Самсонов, версус!)

Answer 2

[Евгений Самсонов]

SELECT a.user_id, a.action, a.dttm
FROM actions a
LEFT JOIN actions a1 ON a1.user_id = a.user_id AND a.dttm < a1.dttm 
WHERE a.user_id IN (1, 2) AND a1.dttm IS NULL;

Для пользователей c id 1, 2. Хорошо бы добавить индекс (user_id, dttm) в таблице действий

Comments

[Радость моя]

индекс есть, но ваш запрос выполняется очень долго (на 150 тыс действий - 20сек). быстрее выходит если вытягивать для каждого юзера последнюю запись из лога действий отдельным запросом. поэтому я не знаю даже, наверно не получится использовать этот вариант.

[Евгений Самсонов]

Радость моя, Странно, несколько млн записей это немного. EXPLAIN что показывает?

[Руслан .]

Немного изменил ваш запрос.
По требуемому результату таблица users не нужна, все необходимое есть в actions.
Кроме этого не нужна зажимка на id, тут видимо дано для примера только два пользователя,
по факту их может быть сколько угодно.

В итоге вот так выходит:

SELECT act.user_id, act.action_name AS last_action, act.dttm AS last_action_date
  FROM actions act
  LEFT JOIN actions act1
         ON act1.user_id = act.user_id
        AND act1.dttm    > act.dttm
 WHERE act1.user_id IS NULL

[Радость моя]

Евгений Самсонов,

[Радость моя]

Руслан ., спасибо за ваш пример. но он такой же долгий как и оригинал :(
скорее всего придется дергать последнюю запись для каждого юзера отдельным запросом.

[Евгений Самсонов]

Радость моя, да вроде все по индексам, должно быть быстро. Как вариант, сделать составной индекс на user_id, dttm. Во втором JOIN, думаю, rows сократиться

И можно исключить из запроса таблицу users, как предлагает Руслан ., если данные из нее не нужны

[netyshka]

Радость моя, а я, а я , а посмотри мой на быстроту)