MySQL: Optimization GROUP BY multiple keys

问题:

I have a table PAYMENTS in MySql database:

CREATE TABLE `PAYMENTS` (
    `ID` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `USER_ID` BIGINT(20) NOT NULL,
    `CATEGORY_ID` BIGINT(20) NOT NULL,
    `AMOUNT` DOUBLE NULL DEFAULT NULL,
    PRIMARY KEY (`ID`),
    INDEX `PAYMENT_INDEX1` (`USER_ID`),
    INDEX `PAYMENT_INDEX2` (`CATEGORY_ID`),
    INDEX `PAYMENT_INDEX3` (`CATEGORY_ID`, `USER_ID`)
) ENGINE=InnoDB;

I want to get summary amount for each user in ech category. Here is script:

select sum(AMOUNT), USER_ID, CATEGORY_ID
from PAYMENTS
group by USER_ID, CATEGORY_ID;

MySql's EXPLAIN command shows Extra: "Using temporary; Using filesort"

How to rid of Using temporary & filesort?

回答1:

You have an index on (CATEGORY_ID, USER_ID) but you're grouping by USER_ID, CATEGORY_ID.

Order matters! Create an index that covers the GROUP BY clause you're actually using — with the fields in the same order.