mysql query performance index

Question

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

SELECT COUNT(sid), COUNT(DISTINCT(cid)), shop 
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` (
  `sid` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `f_shop` INT(11) NOT NULL,
  `f_offer` INT(11) DEFAULT NULL,
  `cid` CHAR(32) DEFAULT NULL,
  `f_partner` VARCHAR(20) NOT NULL,
   .
   . 
   .
   +8-10
   .
   .
   .
  `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`)
) ENGINE=INNODB AUTO_INCREMENT=10747233 DEFAULT CHARSET=latin1

Actually there are more then 5million rows.

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 09: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

score 2 · Answer 1 · answered Oct 21 '11 at 09:47

2

You need an compound index

ALTER TABLE forwarding ADD INDEX shopdate (shop, fDate)

answered Oct 21 '11 at 09:47

Johan

74,508
24
191
319

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
1

@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 07:29

score 1 · Accepted Answer · edited May 23 '17 at 11:55

1

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

a clustered primary key

http://dev.mysql.com/doc/refman/5.0/en/innodb-index-types.html

http://www.xaprb.com/blog/2006/07/04/how-to-exploit-mysql-index-optimizations/

MySQL and NoSQL: Help me to choose the right one

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
)
engine=innodb;

hope this helps :)

edited May 23 '17 at 11:55

Community

1
1

answered Oct 21 '11 at 10:59

Jon Black

16,223
5
43
42

hello, the count of data doesn't allow me to add a new primary key. – user954740 Oct 24 '11 at 07:12
i can't drop the existing primary key because it's an auto increment. – user954740 Oct 24 '11 at 08: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 ? – Jon Black Oct 24 '11 at 09:44

score 0 · Answer 3 · answered Oct 21 '11 at 09:47

0

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

answered Oct 21 '11 at 09:47

Sashi Kant

13,277
9
44
71

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

score 0 · Answer 4 · answered Oct 21 '11 at 09:50

~~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.

mysql query performance index

4 Answers4