Re: Corruption? Performance issue + strange 'explain'
Hi, Have you tried changing the date format like delete from TelecomAccountPosting where LocID=19014 and InvDate='2008-01-15'; I know it shouldn't matter but I have had similar issues due to the date format. Also are you sure there are 3773 records matching this criteria - according to your table structure there is an UNIQUE index on LocID and InvDate so there should not be more than one record. Dobromir Velev WebSitePulse.com On Tuesday 22 January 2008 02:39, Daniel Kasak wrote: > On Tue, 2008-01-22 at 11:23 +1100, Chris wrote: > > > Why is it saying 'Impossible WHERE noticed after reading const tables'? > > > > http://dev.mysql.com/doc/refman/4.1/en/explain.html > > MySQL has read all const (and system) tables and notice that the WHERE > > clause is always false. > > > > ie - no rows match that query and so there's nothing to 'explain'. > > There must be a problem then. In this particular example, there were > 3773 records returned by this select. > > > I'd suspect that the time is spent trying to check or clean up the > > foreign key reference. Are there lots of locations with that id in the > > tlocations table? > > 1 > > > I'd also assume that since it's named 'id' it would be > > a primary key (and indexed) ? > > Indexed, yes. > > I'll go ahead with that restore from a backup tonight. > > -- > 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]
Re: Corruption? Performance issue + strange 'explain'
On Tue, 2008-01-22 at 11:23 +1100, Chris wrote: > > Why is it saying 'Impossible WHERE noticed after reading const tables'? > > http://dev.mysql.com/doc/refman/4.1/en/explain.html > MySQL has read all const (and system) tables and notice that the WHERE > clause is always false. > > ie - no rows match that query and so there's nothing to 'explain'. There must be a problem then. In this particular example, there were 3773 records returned by this select. > I'd suspect that the time is spent trying to check or clean up the > foreign key reference. Are there lots of locations with that id in the > tlocations table? 1 > I'd also assume that since it's named 'id' it would be > a primary key (and indexed) ? Indexed, yes. I'll go ahead with that restore from a backup tonight. -- 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]
Re: Corruption? Performance issue + strange 'explain'
Daniel Kasak wrote: 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 '-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'? http://dev.mysql.com/doc/refman/4.1/en/explain.html MySQL has read all const (and system) tables and notice that the WHERE clause is always false. ie - no rows match that query and so there's nothing to 'explain'. 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'd suspect that the time is spent trying to check or clean up the foreign key reference. Are there lots of locations with that id in the tlocations table? I'd also assume that since it's named 'id' it would be a primary key (and indexed) ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Corruption? Performance issue + strange 'explain'
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 '-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]
