views:

10

answers:

1

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?

A: 
VoteyDisciple
Thanks! I've changed order of fields in index and it works without Extras now!
semitkin