MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE credit_transactions (
   id bigint auto_increment PRIMARY KEY,
   user_id bigint NOT NULL,
   credit_type_id bigint NOT NULL DEFAULT 1,
   message_id bigint,
   delta bigint NOT NULL,
   created_at timestamp,
   updated_at timestamp,
   deleted_at timestamp DEFAULT NULL
) ENGINE=InnoDB;

CREATE INDEX credit_transactions_user_id_delta_idx ON credit_transactions(user_id, delta);

Records: 0  Duplicates: 0  Warnings: 0


CREATE PROCEDURE InsertRand(IN UserID INT, IN NumRows INT, IN MinVal INT, IN MaxVal INT)
    BEGIN
        DECLARE i INT;
        SET i = 1;
        START TRANSACTION;
        WHILE i <= NumRows DO
            INSERT INTO credit_transactions (
                  user_id,
                  credit_type_id,
                  message_id,
                  delta,
                  created_at,
                  updated_at
            ) VALUES (
                  UserID,
                  1,
                  NULL,
                  MinVal + CEIL(RAND() * (MaxVal - MinVal)),
                  NOW(),
                  NOW()
            );
            SET i = i + 1;
        END WHILE;
        COMMIT;
    END;
-- insert 20k transactions for user 1, with random delta from -1000 to 1000
CALL InsertRand(1, 20000, -1000, 1000);
-- insert 20k transactions for user 2, with random delta from -1000 to 1000
CALL InsertRand(2, 20000, -1000, 1000);
EXPLAIN ANALYZE
SELECT SUM(delta) FROM credit_transactions WHERE user_id = 1;


EXPLAIN


-> Aggregate: sum(credit_transactions.delta)  (cost=4014.87 rows=1) (actual time=7.045..7.045 rows=1 loops=1)
    -> Covering index lookup on credit_transactions using credit_transactions_user_id_delta_idx (user_id=1)  (cost=2014.87 rows=20000) (actual time=0.044..5.266 rows=20000 loops=1)



EXPLAIN ANALYZE
  SELECT SUM(delta) FROM credit_transactions WHERE user_id = 2;


EXPLAIN


-> Aggregate: sum(credit_transactions.delta)  (cost=4014.87 rows=1) (actual time=6.580..6.580 rows=1 loops=1)
    -> Covering index lookup on credit_transactions using credit_transactions_user_id_delta_idx (user_id=2)  (cost=2014.87 rows=20000) (actual time=0.030..4.862 rows=20000 loops=1)



SELECT user_id, COUNT(*) FROM credit_transactions GROUP BY user_id;


user_id
COUNT(*)


1
20000

2
20000