Re: DELETE FROM statement seems not to use my index...
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]
DELETE FROM statement seems not to use my index...
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: mysql> explain select count(*) from tblmainupdateinfo WHERE ClientID=638; ++-+---+--+---+- ---+-+---+--+--+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+--+---+- ---+-+---+--+--+ | 1 | SIMPLE | tblmainupdateinfo | ref | ClientID | ClientID | 4 | const | 1 | Using where; Using index | ++-+---+--+---+- ---+-+---+--+--+ 1 row in set (0.03 sec) I'm using mysql 4.1.8-max-nt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
