We have installed mysql-5.1.39 and having a database with following table. CREATE TABLE `EntMsgLog` ( `msgId` bigint(20) NOT NULL, `causeId` bigint(20) NOT NULL, `entityId` int(11) NOT NULL, `msgReceiver` bigint(20) NOT NULL, `msgTextId` int(11) NOT NULL, `flags` bit(8) NOT NULL, `timeStamp` bigint(20) NOT NULL, `credits` float NOT NULL, UNIQUE KEY `causeId` (`causeId`,`msgId`,`timeStamp`), KEY `entityId` (`entityId`), KEY `msgReceiver` (`msgReceiver`), KEY `timeStamp` (`timeStamp`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 /*!50100 PARTITION BY RANGE (timeStamp) (PARTITION p01042010 VALUES LESS THAN (1270146600000) ENGINE = MyISAM, PARTITION p02042010 VALUES LESS THAN (1270233000000) ENGINE = MyISAM, PARTITION p03042010 VALUES LESS THAN (1270319400000) ENGINE = MyISAM, ------------------ 60 such partitions.. ---- PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */ |
my query is following format: mysql> explain select * from EntMsgLog where causeId= 659824157048176974 and msgId = 143168093266866137; +----+-------------+-----------+------+---------------+---------+---------+-------------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------+---------------+---------+---------+-------------+------+-------+ | 1 | SIMPLE | EntMsgLog | ref | causeId | causeId | 16 | const,const | 62 | | +----+-------------+-----------+------+---------------+---------+---------+-------------+------+-------+ This query is taking 1-2 sec. to execute and after profiling the query, I found query is taking 90-95% time in "statistics" state. Please let me know what I am doing wrong. -Dheeraj
