当前位置: 动力学知识库 > 问答 > 编程问答 >

optimization - How to set correct index for this MySql query?

问题描述:

I have this query showing up in MySql slow query log. (It is not slow, but it is not using indexes right). I need some help on how to set up the index right.

SELECT tbladded.amount*SUM(tbladdeditem.amount)

FROM tbladded

INNER JOIN tbladdeditem ON tbladded.addedid = tbladdeditem.addedid AND tbladdeditem.deleted='False'

WHERE tbladded.userid=100

AND tbladded.date='2012-01-01'

AND tbladded.deleted='False'

GROUP BY tbladded.addedid

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE tbladded ref PRIMARY,userid_date userid_date 8 const,const 1 Using where

1 SIMPLE tbladdeditem ref addedid addedid 5 tbladded.addedid 1 Using where

This is how the tables look like:

CREATE TABLE `tbladded` (

`addedid` int(11) NOT NULL AUTO_INCREMENT,

`amount` double DEFAULT NULL,

`date` date DEFAULT NULL,

`userid` mediumint(9) DEFAULT NULL,

`deleted` enum('False','True') CHARACTER SET latin1 DEFAULT 'False',

PRIMARY KEY (`addedid`),

KEY `userid_date` (`userid`,`date`) USING BTREE

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `tbladdeditem` (

`addeditemid` int(11) NOT NULL AUTO_INCREMENT,

`amount` double DEFAULT NULL,

`addedid` int(11) DEFAULT NULL,

`userid` mediumint(9) DEFAULT NULL,

`deleted` enum('False','True') CHARACTER SET latin1 DEFAULT 'False',

PRIMARY KEY (`addeditemid`),

KEY `addedid` (`addedid`),

KEY `userid` (`userid`),

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

网友答案:

try this:

ALTER TABLE `tbladded` ADD INDEX 
`tbladdedIndex` (`userid`, `date`, `deleted`);
分享给朋友:
您可能感兴趣的文章:
随机阅读: