Take the 2-minute tour ×

my following query needs more then two minutes and i don't which index is the best to improve the performance:

FROM forwarding 
WHERE fDate BETWEEN '2011-06-01' AND '2011-06-30' 
GROUP BY shop;

The EXPLAIN result:

id   select_type   table            type    possible_keys                                                        key       key_len    ref       rows     Extra
1    SIMPLE        sal_forwarding   index   forwardDate,forwardDate_2,forwardDate_3,forwardDate_4,forwardDate_5  f_shop    40         (NULL)    2448997  Using where; Using index

The key f_shop has the following structure: (f_shop, forwardDate, cid)

What is the best Index to improve the performance for my query?

Thank you very much.

UPDATE: Here is the table Create Statement:

CREATE TABLE `forwarding` (
  `f_shop` INT(11) NOT NULL,
  `f_offer` INT(11) DEFAULT NULL,
  `cid` CHAR(32) DEFAULT NULL,
  `f_partner` VARCHAR(20) NOT NULL,
  `fDate` DATE NOT NULL,
  PRIMARY KEY (`sid`),
  KEY `f_shop` (`f_shop`,`fDate`,`cid`),
  KEY `f_partner` (`f_partner`,`fDate`),
  KEY `fDate` (`fDate`,`cid`),
  KEY `fDate_2` (`fDate`,`f_shop`),
  KEY `fDate_3` (`fDate`,`f_shop`,`f_partner`),
  KEY `fDate_4` (`fDate`,`f_partner`,`cid`),
  KEY `fDate_5` (`fDate`,`f_affiliateId`)

Actually there are more then 5million rows.

share|improve this question
Please post table structure (SHOW CREATE TABLE forwarding ). Also tell us approximately how many rows do you have in that table? –  Salman A Oct 21 '11 at 9:47
It would be nice if you accepted answers provided for your previous question. It's minimum courtesy you can do for people that helped you. –  N.B. Oct 21 '11 at 10:19
add comment

4 Answers

up vote 1 down vote accepted


a clustered primary key



MySQL, NoSQL: help me to choose the right one! (on a )

60 million entries, select entries from a certain month. How to optimize database?

How to avoid "Using temporary" in many-to-many queries?

however your current clustered PK sid wont be much help so try something along the lines of:

create table forwarding
f_date date not null,
f_shop int unsigned not null,
sid int unsigned not null, -- added for uniqueness
primary key (f_date, f_shop, sid) -- clustered primary key

hope this helps :)

share|improve this answer
hello, the count of data doesn't allow me to add a new primary key. –  user954740 Oct 24 '11 at 7:12
i can't drop the existing primary key because it's an auto increment. –  user954740 Oct 24 '11 at 8:20
i'm sure you can figure out the best composite key combination yourself given the links above ?? do you really need the auto_inc part ? –  f00 Oct 24 '11 at 9:44
add comment

You need an compound index

ALTER TABLE forwarding ADD INDEX shopdate (shop, fDate)
share|improve this answer
this index doesn't work. my existing index is used automatically. It means that my index is better for this query but i don'T think that 2min are good.^^ –  user954740 Oct 21 '11 at 11:28
@user954740, 2.4 million rows in 2 minutes, sound about right. You'll have to cache the cid and sid counts if you want faster results. Create a new table shopcount(shop, cidcount, sidcount). And update that with delete, update and insert triggers on the forwarding table. –  Johan Oct 21 '11 at 11:33
yes, good idea, but this data is too dynamic. I need the data for different Times and with other different rules. –  user954740 Oct 24 '11 at 7:29
add comment

Index the shop column, and 1 more thing u can implement here is using partition by date, ur query will run fast

share|improve this answer
Partitioning by date is overkill a compound index will do the trick. –  Johan Oct 21 '11 at 9:49
Think futuristic, U will need to partition after the number of record doubles...Partition with date –  Sashi Kant Oct 21 '11 at 9:53
add comment

I think you need a key for the forwardDate, since that is the only attribute used in the WHERE clause of your query.

EDIT As noted in other answers, a compound index on shop and forwardDate is the way to go. I missed the last part of the query due to the single line formatting.

share|improve this answer
add comment

Your Answer


