Какое решение вы бы выбрали для пересчёта большого количества записей?

Question

[Gregory House]

Дано:
1. Таблица с пользователями (count = 1 миллион записей)
user_id, sended, received

2. Каждый пользователь отправляет сообщения и принимает сообщения (count = 10 миллионов записей).
Хранятся в таблице
message_id, user_id_from, user_id_to

Цель:
У каждого пользователя обновить счётчики sended/received - т.е. кто сколько отправил и принял сообщений максимально быстро.

Вопрос:
1. Как бы вы решали эту задачу?

Answer 1

[Rsa97]

Если надо пересчитать разово, то

UPDATE `users` 
  LEFT JOIN (
    SELECT `user_id_from`, COUNT(*) AS `count` FROM `messages` GROUP BY `user_id_from`
  ) AS `from` ON `from`.`user_id_from` = `users`.`user_id`
  LEFT JOIN (
    SELECT `user_id_to`, COUNT(*) AS `count` FROM `messages` GROUP BY `user_id_to`
  ) AS `to` ON `to`.`user_id_to` = `users`.`user_id`
  SET `users`.`sended` = IFNULL(`from`.`count`, 0), 
      `users`.`received` = IFNULL(`to`.`count`, 0)

В дальнейшем поддерживать через триггер, как написал Максим Федоров.

Answer 2

[Максим Федоров]

можно использовать триггеры СУБД на соответствующих действиях в таблице сообщений.

CREATE TRIGGER `add_message` BEFORE INSERT ON `messages` FOR EACH ROW BEGIN
UPDATE user SET sended = sended + 1 WHERE user_id = new.user_id_from;
UPDATE user SET received= received + 1 WHERE user_id = new.user_id_to;
END;

ну и не стоит забывать:
1. Про оптимизацию настроек самой СУБД
2. Про индексы
3. Про партицирование при необходимости