Hi, You probably aren't doing anything wrong, per se, but I suspect Handler::info() is slow and is being called once per partition. You should probably start looking at your system overall to check where the time is spent. Is it in reading from disk? If so, can you make it read from memory instead, or if that's not possible, are your disks slower than they should be...? and so on. What OS are you running?
On Mon, Apr 26, 2010 at 5:59 AM, Dheeraj Kumar <dksid...@gmail.com> wrote: > 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 > -- Baron Schwartz Percona Inc <http://www.percona.com/> Consulting, Training, Support & Services for MySQL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
