MySQL LEFT JOIN with SUM is slow

Question

I need ideas to optimize this query:

SELECT U.Id
    , U.Name
    , SUM(P.Credit) AS CreditAmount
    , SUM(P.Debit) AS DebitAmount
    FROM users U
    LEFT JOIN payments P ON P.UserId = U.Id
    WHERE U.StatusId = 1 /* Active Users Only */
    GROUP BY U.Id

Some information about the tables:

1. users table: 10,000+ records
2. payments table: 2,000,000+ records, indexed on UserId column

The query takes up to 2 minutes. By investigating the query, I found out that the SUM function is the reason why the query is slow. Tried executing the query without the SUM takes less than 2 seconds.

Is there any room to improve this query? Thanks

EDIT 1

This is what I got when using the EXPLAIN:

id  select_type table   type    possible_keys   key     key_len     ref             rows    Extra
1   SIMPLE      U       ALL                                                         8304    Using where; Using temporary; Using filesort
1   SIMPLE      P       ref     UserId          UserId  5           Database.U.Id   361     ""

The index payments.UserId is being used in the query. Any thoughts on this?

EDIT 2 Table Information:

users   "CREATE TABLE `users` (
  `Id` int(11) NOT NULL auto_increment,
  `StatusId` int(11) default NULL,
  `Name` varchar(60) default NULL,
  `TimeZone` varchar(100) default NULL,
  PRIMARY KEY  (`Id`),
  KEY `StatusId` (`StatusId`),
  CONSTRAINT `FK_User_Status` FOREIGN KEY (`StatusId`) REFERENCES `userStatus` (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8"

payments    "CREATE TABLE `payments` (
  `Id` int(11) NOT NULL auto_increment,
  `UserId` int(11) default NULL,  
  `Description` varchar(200) default NULL,
  `Debit` decimal(11,2) default NULL,
  `Credit` decimal(11,2) default NULL,
  `Date` datetime default NULL,
  PRIMARY KEY  (`Id`),
  KEY `UserId` (`UserId`),
  CONSTRAINT `FK_Payment_User` FOREIGN KEY (`UserId`) REFERENCES `users` (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8"

Answer 1

Kael, here's the script I ran, and the output from it, to show the indexes and query I provided in my first answer reduced the query time from about 14.3 seconds to just 0.75 seconds:

DROP TABLE IF EXISTS payments;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS userStatus;

CREATE TABLE userStatus (
    Id int(11) NOT NULL auto_increment,
    PRIMARY KEY  (Id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE users (
    Id int(11) NOT NULL auto_increment,
    StatusId int(11) default NULL,
    Name varchar(60) default NULL,
    TimeZone varchar(100) default NULL,
    PRIMARY KEY  (Id),
    KEY StatusId (StatusId),
    CONSTRAINT FK_User_Status FOREIGN KEY (StatusId) REFERENCES userStatus (Id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE payments (
    Id int(11) NOT NULL auto_increment,
    UserId int(11) default NULL,  
    Description varchar(200) default NULL,
    Debit decimal(11,2) default NULL,
    Credit decimal(11,2) default NULL,
    Date datetime default NULL,
    PRIMARY KEY  (Id),
    KEY UserId (UserId),
    CONSTRAINT FK_Payment_User FOREIGN KEY (UserId) REFERENCES users (Id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS ids;
CREATE TABLE IF NOT EXISTS ids (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL,
    PRIMARY KEY (id)
) ENGINE=MyISAM; -- must be MyISAM
INSERT INTO ids SET id = NULL; # inserts a 1
UPDATE ids SET id=0;
ALTER TABLE ids AUTO_INCREMENT=1;
INSERT INTO ids SELECT NULL FROM ids; #      1      1
INSERT INTO ids SELECT NULL FROM ids; #      2      2
INSERT INTO ids SELECT NULL FROM ids; #      4      4
INSERT INTO ids SELECT NULL FROM ids; #      8      8
INSERT INTO ids SELECT NULL FROM ids; #     10     16
INSERT INTO ids SELECT NULL FROM ids; #     20     32
INSERT INTO ids SELECT NULL FROM ids; #     40     64
INSERT INTO ids SELECT NULL FROM ids; #     80    128
INSERT INTO ids SELECT NULL FROM ids; #    100    256
INSERT INTO ids SELECT NULL FROM ids; #    200    512
INSERT INTO ids SELECT NULL FROM ids; #    400  1,024
INSERT INTO ids SELECT NULL FROM ids; #    800  2,048
INSERT INTO ids SELECT NULL FROM ids; #   1000  4,096
INSERT INTO ids SELECT NULL FROM ids; #   2000  8,192
INSERT INTO ids SELECT NULL FROM ids; #   4000 16,384

INSERT INTO 
userStatus
(id)
SELECT
id
FROM
ids
WHERE
id > 0
ORDER BY
id
LIMIT 2;

INSERT INTO 
users
(Id, StatusId)
SELECT
id,
IF(RAND() < .9, 1, 2) -- 90% of users are 'Active'
FROM
ids
WHERE
id > 0 AND
id <= 20000
ORDER BY
id;

INSERT INTO
payments
(Id, UserId, Debit, Credit, `Date`)
SELECT
NULL,
i1.id,
ROUND(RAND() * 1000, 2),
ROUND(RAND() * 1000, 2),
NOW() - INTERVAL FLOOR(RAND() * 86400 * 1000) SECOND
FROM
ids i1,
ids i2 
WHERE
i1.id > 0 AND
i1.id <= 20000 AND
i2.id < 100
ORDER BY
RAND();

EXPLAIN
SELECT U.Id
    , U.Name
    , SUM(P.Credit) AS CreditAmount
    , SUM(P.Debit) AS DebitAmount
    FROM users U
    LEFT JOIN payments P ON P.UserId = U.Id
    WHERE U.StatusId = 1 /* Active Users Only */
    GROUP BY U.Id;

SELECT U.Id
    , U.Name
    , SUM(P.Credit) AS CreditAmount
    , SUM(P.Debit) AS DebitAmount
    FROM users U
    LEFT JOIN payments P ON P.UserId = U.Id
    WHERE U.StatusId = 1 /* Active Users Only */
    GROUP BY U.Id;

SELECT U.Id
    , U.Name
    , SUM(P.Credit) AS CreditAmount
    , SUM(P.Debit) AS DebitAmount
    FROM users U
    LEFT JOIN payments P ON P.UserId = U.Id
    WHERE U.StatusId = 1 /* Active Users Only */
    GROUP BY U.Id;

SELECT U.Id
    , U.Name
    , SUM(P.Credit) AS CreditAmount
    , SUM(P.Debit) AS DebitAmount
    FROM users U
    LEFT JOIN payments P ON P.UserId = U.Id
    WHERE U.StatusId = 1 /* Active Users Only */
    GROUP BY U.Id;

CREATE INDEX ix_status ON users (StatusId, Id);
CREATE INDEX ix_userid ON payments (UserId, Credit, Debit);

EXPLAIN
SELECT U.Id
    , U.Name
    , SUM(P.Credit) AS CreditAmount
    , SUM(P.Debit) AS DebitAmount
    FROM 
        users U FORCE INDEX (ix_status)
    LEFT JOIN 
        payments P FORCE INDEX (ix_userid) ON P.UserId = U.Id
    WHERE 
        U.StatusId = 1 /* Active Users Only */
    GROUP BY
        U.Id;

SELECT U.Id
    , U.Name
    , SUM(P.Credit) AS CreditAmount
    , SUM(P.Debit) AS DebitAmount
    FROM 
        users U FORCE INDEX (ix_status)
    LEFT JOIN 
        payments P FORCE INDEX (ix_userid) ON P.UserId = U.Id
    WHERE 
        U.StatusId = 1 /* Active Users Only */
    GROUP BY
        U.Id;

SELECT U.Id
    , U.Name
    , SUM(P.Credit) AS CreditAmount
    , SUM(P.Debit) AS DebitAmount
    FROM 
        users U FORCE INDEX (ix_status)
    LEFT JOIN 
        payments P FORCE INDEX (ix_userid) ON P.UserId = U.Id
    WHERE 
        U.StatusId = 1 /* Active Users Only */
    GROUP BY
        U.Id;

SELECT U.Id
    , U.Name
    , SUM(P.Credit) AS CreditAmount
    , SUM(P.Debit) AS DebitAmount
    FROM 
        users U FORCE INDEX (ix_status)
    LEFT JOIN 
        payments P FORCE INDEX (ix_userid) ON P.UserId = U.Id
    WHERE 
        U.StatusId = 1 /* Active Users Only */
    GROUP BY
        U.Id; 

and here's the output:

[ross@titanv ~]$ mysql -vvv < ex.sql temp
--------------
DROP TABLE IF EXISTS payments
--------------

Query OK, 0 rows affected (0.01 sec)

--------------
DROP TABLE IF EXISTS users
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
DROP TABLE IF EXISTS userStatus
--------------

Query OK, 0 rows affected (0.01 sec)

--------------
CREATE TABLE userStatus (
        Id int(11) NOT NULL auto_increment,
        PRIMARY KEY  (Id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
--------------

Query OK, 0 rows affected (0.01 sec)

--------------
CREATE TABLE users (
        Id int(11) NOT NULL auto_increment,
        StatusId int(11) default NULL,
        Name varchar(60) default NULL,
        TimeZone varchar(100) default NULL,
        PRIMARY KEY  (Id),
        KEY StatusId (StatusId),
        CONSTRAINT FK_User_Status FOREIGN KEY (StatusId) REFERENCES userStatus (Id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
CREATE TABLE payments (
        Id int(11) NOT NULL auto_increment,
        UserId int(11) default NULL,  
        Description varchar(200) default NULL,
        Debit decimal(11,2) default NULL,
        Credit decimal(11,2) default NULL,
        Date datetime default NULL,
        PRIMARY KEY  (Id),
        KEY UserId (UserId),
        CONSTRAINT FK_Payment_User FOREIGN KEY (UserId) REFERENCES users (Id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
--------------

Query OK, 0 rows affected (0.01 sec)

--------------
DROP TABLE IF EXISTS ids
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
CREATE TABLE IF NOT EXISTS ids (
        id INT UNSIGNED AUTO_INCREMENT NOT NULL,
        PRIMARY KEY (id)
) ENGINE=MyISAM
--------------

Query OK, 0 rows affected (0.01 sec)

--------------
INSERT INTO ids SET id = NULL
--------------

Query OK, 1 row affected (0.00 sec)

--------------
UPDATE ids SET id=0
--------------

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

--------------
ALTER TABLE ids AUTO_INCREMENT=1
--------------

Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

--------------
INSERT INTO ids SELECT NULL FROM ids
--------------

Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

--------------
INSERT INTO ids SELECT NULL FROM ids
--------------

Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

--------------
INSERT INTO ids SELECT NULL FROM ids
--------------

Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

--------------
INSERT INTO ids SELECT NULL FROM ids
--------------

Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

--------------
INSERT INTO ids SELECT NULL FROM ids
--------------

Query OK, 16 rows affected (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 0

--------------
INSERT INTO ids SELECT NULL FROM ids
--------------

Query OK, 32 rows affected (0.01 sec)
Records: 32  Duplicates: 0  Warnings: 0

--------------
INSERT INTO ids SELECT NULL FROM ids
--------------

Query OK, 64 rows affected (0.00 sec)
Records: 64  Duplicates: 0  Warnings: 0

--------------
INSERT INTO ids SELECT NULL FROM ids
--------------

Query OK, 128 rows affected (0.00 sec)
Records: 128  Duplicates: 0  Warnings: 0

--------------
INSERT INTO ids SELECT NULL FROM ids
--------------

Query OK, 256 rows affected (0.00 sec)
Records: 256  Duplicates: 0  Warnings: 0

--------------
INSERT INTO ids SELECT NULL FROM ids
--------------

Query OK, 512 rows affected (0.00 sec)
Records: 512  Duplicates: 0  Warnings: 0

--------------
INSERT INTO ids SELECT NULL FROM ids
--------------

Query OK, 1024 rows affected (0.00 sec)
Records: 1024  Duplicates: 0  Warnings: 0

--------------
INSERT INTO ids SELECT NULL FROM ids
--------------

Query OK, 2048 rows affected (0.00 sec)
Records: 2048  Duplicates: 0  Warnings: 0

--------------
INSERT INTO ids SELECT NULL FROM ids
--------------

Query OK, 4096 rows affected (0.01 sec)
Records: 4096  Duplicates: 0  Warnings: 0

--------------
INSERT INTO ids SELECT NULL FROM ids
--------------

Query OK, 8192 rows affected (0.01 sec)
Records: 8192  Duplicates: 0  Warnings: 0

--------------
INSERT INTO ids SELECT NULL FROM ids
--------------

Query OK, 16384 rows affected (0.02 sec)
Records: 16384  Duplicates: 0  Warnings: 0

--------------
INSERT INTO 
userStatus
(id)
SELECT
id
FROM
ids
WHERE
id > 0
ORDER BY
id
LIMIT 2
--------------

Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

--------------
INSERT INTO 
users
(Id, StatusId)
SELECT
id,
IF(RAND() < .9, 1, 2) 
FROM
ids
WHERE
id > 0 AND
id <= 20000
ORDER BY
id
--------------

Query OK, 20000 rows affected (0.11 sec)
Records: 20000  Duplicates: 0  Warnings: 0

--------------
INSERT INTO
payments
(Id, UserId, Debit, Credit, `Date`)
SELECT
NULL,
i1.id,
ROUND(RAND() * 1000, 2),
ROUND(RAND() * 1000, 2),
NOW() - INTERVAL FLOOR(RAND() * 86400 * 1000) SECOND
FROM
ids i1,
ids i2 
WHERE
i1.id > 0 AND
i1.id <= 20000 AND
i2.id < 100
ORDER BY
RAND()
--------------

Query OK, 2000000 rows affected (21.52 sec)
Records: 2000000  Duplicates: 0  Warnings: 0

--------------
EXPLAIN
SELECT U.Id
    , U.Name
    , SUM(P.Credit) AS CreditAmount
    , SUM(P.Debit) AS DebitAmount
    FROM users U
    LEFT JOIN payments P ON P.UserId = U.Id
    WHERE U.StatusId = 1 
    GROUP BY U.Id
--------------

+----+-------------+-------+------+---------------+----------+---------+-----------+-------+-------------+
| id | select_type | table | type | possible_keys | key      | key_len | ref       | rows  | Extra       |
+----+-------------+-------+------+---------------+----------+---------+-----------+-------+-------------+
|  1 | SIMPLE      | U     | ref  | StatusId      | StatusId | 5       | const     | 10274 | Using where |
|  1 | SIMPLE      | P     | ref  | UserId        | UserId   | 5       | temp.U.Id |     1 |             |
+----+-------------+-------+------+---------------+----------+---------+-----------+-------+-------------+
2 rows in set (0.00 sec)

--------------
SELECT U.Id
    , U.Name
    , SUM(P.Credit) AS CreditAmount
    , SUM(P.Debit) AS DebitAmount
    FROM users U
    LEFT JOIN payments P ON P.UserId = U.Id
    WHERE U.StatusId = 1 
    GROUP BY U.Id
--------------

+-------+------+--------------+-------------+
| Id    | Name | CreditAmount | DebitAmount |
+-------+------+--------------+-------------+
|     1 | NULL |     47824.73 |    49580.71 |
|     2 | NULL |     46426.02 |    52019.69 |
...
| 19999 | NULL |     50041.10 |    47696.88 |
| 20000 | NULL |     51923.69 |    50349.38 |
+-------+------+--------------+-------------+
18057 rows in set (14.60 sec)

--------------
SELECT U.Id
    , U.Name
    , SUM(P.Credit) AS CreditAmount
    , SUM(P.Debit) AS DebitAmount
    FROM users U
    LEFT JOIN payments P ON P.UserId = U.Id
    WHERE U.StatusId = 1 
    GROUP BY U.Id
--------------

+-------+------+--------------+-------------+
| Id    | Name | CreditAmount | DebitAmount |
+-------+------+--------------+-------------+
|     1 | NULL |     47824.73 |    49580.71 |
|     2 | NULL |     46426.02 |    52019.69 |
...
| 19999 | NULL |     50041.10 |    47696.88 |
| 20000 | NULL |     51923.69 |    50349.38 |
+-------+------+--------------+-------------+
18057 rows in set (14.10 sec)

--------------
SELECT U.Id
    , U.Name
    , SUM(P.Credit) AS CreditAmount
    , SUM(P.Debit) AS DebitAmount
    FROM users U
    LEFT JOIN payments P ON P.UserId = U.Id
    WHERE U.StatusId = 1 
    GROUP BY U.Id
--------------

+-------+------+--------------+-------------+
| Id    | Name | CreditAmount | DebitAmount |
+-------+------+--------------+-------------+
|     1 | NULL |     47824.73 |    49580.71 |
|     2 | NULL |     46426.02 |    52019.69 |
...
| 19999 | NULL |     50041.10 |    47696.88 |
| 20000 | NULL |     51923.69 |    50349.38 |
+-------+------+--------------+-------------+
18057 rows in set (14.17 sec)

--------------
CREATE INDEX ix_status ON users (StatusId, Id)
--------------

Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

--------------
CREATE INDEX ix_userid ON payments (UserId, Credit, Debit)
--------------

Query OK, 0 rows affected (5.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

--------------
EXPLAIN
SELECT U.Id
    , U.Name
    , SUM(P.Credit) AS CreditAmount
    , SUM(P.Debit) AS DebitAmount
    FROM 
        users U FORCE INDEX (ix_status)
    LEFT JOIN 
        payments P FORCE INDEX (ix_userid) ON P.UserId = U.Id
    WHERE 
        U.StatusId = 1 
    GROUP BY
        U.Id
--------------

+----+-------------+-------+------+---------------+-----------+---------+-----------+-------+-------------+
| id | select_type | table | type | possible_keys | key       | key_len | ref       | rows  | Extra       |
+----+-------------+-------+------+---------------+-----------+---------+-----------+-------+-------------+
|  1 | SIMPLE      | U     | ref  | ix_status     | ix_status | 5       | const     | 10274 | Using where |
|  1 | SIMPLE      | P     | ref  | ix_userid     | ix_userid | 5       | temp.U.Id | 10002 | Using index |
+----+-------------+-------+------+---------------+-----------+---------+-----------+-------+-------------+
2 rows in set (0.00 sec)

--------------
SELECT U.Id
    , U.Name
    , SUM(P.Credit) AS CreditAmount
    , SUM(P.Debit) AS DebitAmount
    FROM 
        users U FORCE INDEX (ix_status)
    LEFT JOIN 
        payments P FORCE INDEX (ix_userid) ON P.UserId = U.Id
    WHERE 
        U.StatusId = 1 
    GROUP BY
        U.Id
--------------

+-------+------+--------------+-------------+
| Id    | Name | CreditAmount | DebitAmount |
+-------+------+--------------+-------------+
|     1 | NULL |     47824.73 |    49580.71 |
|     2 | NULL |     46426.02 |    52019.69 |
...
| 19999 | NULL |     50041.10 |    47696.88 |
| 20000 | NULL |     51923.69 |    50349.38 |
+-------+------+--------------+-------------+
18057 rows in set (0.75 sec)

--------------
SELECT U.Id
    , U.Name
    , SUM(P.Credit) AS CreditAmount
    , SUM(P.Debit) AS DebitAmount
    FROM 
        users U FORCE INDEX (ix_status)
    LEFT JOIN 
        payments P FORCE INDEX (ix_userid) ON P.UserId = U.Id
    WHERE 
        U.StatusId = 1 
    GROUP BY
        U.Id
--------------

+-------+------+--------------+-------------+
| Id    | Name | CreditAmount | DebitAmount |
+-------+------+--------------+-------------+
|     1 | NULL |     47824.73 |    49580.71 |
|     2 | NULL |     46426.02 |    52019.69 |
...
| 19999 | NULL |     50041.10 |    47696.88 |
| 20000 | NULL |     51923.69 |    50349.38 |
+-------+------+--------------+-------------+
18057 rows in set (0.75 sec)

--------------
SELECT U.Id
    , U.Name
    , SUM(P.Credit) AS CreditAmount
    , SUM(P.Debit) AS DebitAmount
    FROM 
        users U FORCE INDEX (ix_status)
    LEFT JOIN 
        payments P FORCE INDEX (ix_userid) ON P.UserId = U.Id
    WHERE 
        U.StatusId = 1 
    GROUP BY
        U.Id
--------------

+-------+------+--------------+-------------+
| Id    | Name | CreditAmount | DebitAmount |
+-------+------+--------------+-------------+
|     1 | NULL |     47824.73 |    49580.71 |
|     2 | NULL |     46426.02 |    52019.69 |
...
| 19999 | NULL |     50041.10 |    47696.88 |
| 20000 | NULL |     51923.69 |    50349.38 |
+-------+------+--------------+-------------+
18057 rows in set (0.75 sec)

Bye

Answer 2

According this link, I can't speed up the SUM function. The main issue here is that I'm selecting a huge number of records.

I thought of redesigning the users table, adding a new column like TotalPayment which will hold the total credit minus debit amount from the payments table. I know it disobeys the database normalization rules, but I guess this redesign is for saving performance.

Thanks a lot for your help guys.

Answer 3

If you add the following indexes:

CREATE INDEX ix_status ON users (StatusId, Id);
CREATE INDEX ix_userid ON payments (UserId, Credit, Debit);

And add FORCE INDEX options:

SELECT U.Id
    , U.Name
    , SUM(P.Credit) AS CreditAmount
    , SUM(P.Debit) AS DebitAmount
    FROM 
        users U FORCE INDEX (ix_status)
    LEFT JOIN 
        payments P FORCE INDEX (ix_userid) ON P.UserId = U.Id
    WHERE 
        U.StatusId = 1 /* Active Users Only */
    GROUP BY
        U.Id;

Your query will almost certainly perform better.