CODEWITHSUNDEEP
phpmysqlindexing
user954740
user954740

Reputation: 219

mysql query performance index

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.

Upvotes: 2

Views: 329

Answers (4)

Jon Black
Jon Black

Reputation: 16559

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

Upvotes: 1

Jan-Henk
Jan-Henk

Reputation: 4874

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.

Upvotes: 0

Johan
Johan

Reputation: 76537

You need an compound index 

ALTER TABLE forwarding ADD INDEX shopdate (shop, fDate)

Upvotes: 2

Sashi Kant
Sashi Kant

Reputation: 13455

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

Upvotes: 0

Related Questions