Ruben Edna wrote:
Here is create table def: CREATE TABLE `tblmainupdateinfo` ( `MainID` int(11) unsigned NOT NULL default '0', `ClientID` int(11) unsigned NOT NULL default '0', `UpdateInfo` tinyint(4) unsigned NOT NULL default '0', PRIMARY KEY (`MainID`,`ClientID`), KEY `ClientID` (`ClientID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1
The tables holds about about 7 million records. Different MainID's are at
about 40.000 and different ClientID's at about 600 (table will max out at
about 24 million records).
On this table I often do the following: DELETE FROM tblmainupdateinfo WHERE ClientID=XXX;
I would expect MySQL to then use the ClientID Index. However I don't think
it does... because on my laptop it takes all between 10-60sec to delete
(depends on how many WHERE clause matches). When I remove the PRIMARY index
the delete takes about only 0.2-2.0sec. I assume it then acutally uses the
ClientID index. Why does it not when the PRIMARY index exists? I have also
tried to set the PRIMARY KEY in reverse ordrer (`ClientID`,`MainID`), still
having the ClientID index and then it also takes only about 0.2-2.0sec. It seems to me that the DELETE statement only tries to use the first index
available whatever else index that exists... Might this be a bug...?
Yes, I have tries to use EXPLAIN SELECT... and then it does it correct like
this:
In addition to deleting the record, both indicies must be updated.
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
