Is this just something we have to live with or does MySQL 4 handle this better?
Brian. ----- Original Message ----- From: "Andrew Schmidt" <[EMAIL PROTECTED]> To: "Brian Moon" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Thursday, January 24, 2002 3:11 PM Subject: RE: Slow updates with two keys in where. MySQL can only use 1 index per query. So take your first query for example: update forum set approved='N' where id=644122 or thread=644122 it can only use the id key OR the thread key, not both. and since you have an 'or' in your query MySQL must scan the entire table for either id being 644122 or thread being 644122. thanks, -- Andrew -----Original Message----- From: Brian Moon [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 24, 2002 3:03 PM To: [EMAIL PROTECTED] Subject: Slow updates with two keys in where. I have pasted below some queries and a table structure that I am finding to be very slow. As you can see, if I or the two keyed fields, the query takes ~7 seconds. If I just run them by themselves, it takes no measurable time. Is this something we just have to live with? Or, is there something wrong? Brian. ================================================= mysql> update forum set approved='N' where id=644122 or thread=644122; Query OK, 0 rows affected (6.79 sec) Rows matched: 1 Changed: 0 Warnings: 0 mysql> update forum set approved='N' where id=644122; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 mysql> update forum set approved='N' where thread=644122; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 mysql> select count(*) from forum; +----------+ | count(*) | +----------+ | 547989 | +----------+ 1 row in set (0.00 sec) CREATE TABLE `forum` ( `id` int(10) unsigned NOT NULL default '0', `datestamp` datetime NOT NULL default '0000-00-00 00:00:00', `thread` int(10) unsigned NOT NULL default '0', `parent` int(10) unsigned NOT NULL default '0', `author` char(37) NOT NULL default '', `subject` char(255) NOT NULL default '', `email` char(200) NOT NULL default '', `host` char(50) NOT NULL default '', `email_reply` char(1) NOT NULL default 'N', `approved` char(1) NOT NULL default 'N', `msgid` char(100) NOT NULL default '', `modifystamp` int(10) unsigned NOT NULL default '0', `userid` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`id`), KEY `author` (`author`), KEY `userid` (`userid`), KEY `datestamp` (`datestamp`), KEY `subject` (`subject`), KEY `thread` (`thread`), KEY `parent` (`parent`), KEY `approved` (`approved`), KEY `msgid` (`msgid`), KEY `modifystamp` (`modifystamp`) ) Brian Moon ------------------------------------------------------------------------- Phorum Dev Team - http://phorum.org Making better forums with PHP ------------------------------------------------------------------------- --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
