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

This:
1. Table with users (count = 1 million records)

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
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),

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;