Greetings. I have a script which seems to be bringing our server to its knees.
I've isolated one query which is doing it: delete from TelecomAccountPosting where LocID = 19014 and InvDate = '20080115'; I have an index which covers both LocID and InvDate: CREATE TABLE `TelecomAccountPosting` ( `ID` mediumint(8) unsigned NOT NULL auto_increment, `LocID` mediumint(8) unsigned NOT NULL default '0', `BatchNo` mediumint(8) unsigned NOT NULL default '0', `AccountNo` varchar(20) NOT NULL default '', `InvDate` date NOT NULL default '0000-00-00', PRIMARY KEY (`ID`), UNIQUE KEY `IDX_LocID_InvDate` (`LocID`,`InvDate`), KEY `IDX_AccountNo` (`AccountNo`), KEY `IDX_LocID` (`LocID`), KEY `IDX_BatchNo` (`BatchNo`), CONSTRAINT `0_21` FOREIGN KEY (`LocID`) REFERENCES `TLocations` (`ID`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=111073 DEFAULT CHARSET=latin1 If I do an 'explain' on a *select* with the above values, I get: mysql> explain select * from TelecomAccountPosting where LocID = 19014 and InvDate = '20080115'; +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ 1 row in set (0.00 sec) mysql> Why is it saying 'Impossible WHERE noticed after reading const tables'? Is this what the problem is? Every other such delete query PRIOR to this one executes in WAY less than 1 second. But from this query on, the deletes take well over a minute, and MySQL has a GREAT deal of problem servicing all other database clients. I've tried doing a 'check tables' thing from the MySQL administrator ( the longest one ... 'changed' ). It didn't find anything. Is it time to drop everything and restore from a backup? -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
