Hi All,
Given a fairly simple table as follows CREATE TABLE `common_userx2` ( `t_proj` char(6) default NULL, `t_id` int(11) NOT NULL default '0', `t_nick` varchar(50) character set latin1 collate latin1_bin NOT NULL default '', `t_country` varchar(50) NOT NULL default '', `t_cpid` varchar(50) NOT NULL default '', `t_url` varchar(50) default NULL, `t_create_date` int(11) default NULL, `t_create_time` bigint(20) NOT NULL, `t_has_profile` char(1) NOT NULL, `t_team0` int(11) default NULL, `t_metric1` double(20,6) NOT NULL default '0.000000', `t_metric2` double NOT NULL default '0', `t_metric3` double NOT NULL default '0', `t_metric4` double default NULL, `t_active` char(1) NOT NULL default '', `t_rev_metric1` double(20,6) NOT NULL default '10000000000.000000', `t_projrank0` int(11) default NULL, `t_rev_metric2` double(20,6) NOT NULL default '10000000000.000000', `t_racrank0` int(11) default NULL, `t_teamrank0` int(11) default NULL, `t_countryrank0` int(11) default NULL, `t_createdaterank0` int(11) default NULL, PRIMARY KEY (`t_id`), KEY `prank` (`t_rev_metric1`,`t_id`), KEY `trank` (`t_team0`,`t_rev_metric1`,`t_id`), KEY `countryrank` (`t_country`,`t_rev_metric1`,`t_id`), KEY `createdaterank` (`t_create_date`,`t_rev_metric1`,`t_id`), KEY `racrank` (`t_rev_metric2`,`t_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 I have a ranking update statement as follows set @rank = 0; update common_userx2 set projrank0 = @rank:[EMAIL PROTECTED] order by t_rev_metric1,t_id; For the largest case this has close to 1M rows. For weeks it was taking around 10seconds to do this. Yesterday I replaced the main data drive in the machine with a faster SATA Raptor drive. No problems occurred, but since then (and the subsequent reboot of the machine) this particular query is taking 45 minutes! I can't, for the life of me figure out why performance would be degraded so much. At first I thought perhaps it might be just disk/mysql caching but the performance has not increased any in subsequent runs. Any advice on where to look ? Phil -- Help build our city at http://free-dc.myminicity.com !
