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 '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'?
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]
