What solution would you choose for counting a large number of records?

This:
1. Table with users (count = 1 million records)
user_id, sended, received

2. Each user sends messages to and receives messages (count = 10 million records).
Are stored in a table
message_id, user_id_from, user_id_to

Goal:
Each user to update the counters sended/received - i.e. who how many was sent and received messages as quickly as possible.

Question:
1. How would you solve this problem?
July 8th 19 at 15:44

Related questions

More answers about "What solution would you choose for counting a large number of records?"
2 answers
July 8th 19 at 15:46
Solution
If you need to count single
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)

In further support through the trigger as written .
July 8th 19 at 15:48
you can use the triggers of the DBMS on the appropriate course of action in the message table. 
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;

Oh and don't forget:
1. About the optimization of the settings of the DBMS
2. About indexes
3. About protezirovanie if necessary

Find more questions by tags SQL