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 '-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
Re: Slow updates with two keys in where.
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 '-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
Re: Slow updates with two keys in where.
Is this just something we have to live with or does MySQL 4 handle this better? Something to live with. Someday, I'm sure MySQL will optimize this case. Conceptually its not that hard. You split it into multiple queries that track an index. If it is a select, you also UNION the results back together. However, conceptually easy does not mean trivial. That is a lot of work for the MySQL team to do and not very high on their list. Better for you to change: update forum set approved='N' where id=644122 or thread=644122 into update forum set approved='N' where thread=644122 update forum set approved='N' where id=644122 Of course, as a (sort-of) competitor, I probably shouldn't say that. BTW - out of curiosity, what sort of through-put does Phorum have? We have two machines, one web and one mysql, that handle 100m pv/m, which ought to hold to 150m/m before we need more hardware. Sincerely, Steven Roussey http://Network54.com/?pp=e In honor of Jeremy: MySQL 3.23.47-log: up 5 days, processed 595,464,211 queries (1,350/sec. avg) :) - 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
Re: Slow updates with two keys in where.
On Thu, Jan 24, 2002 at 07:34:14PM -0800, Steven Roussey wrote: In honor of Jeremy: MySQL 3.23.47-log: up 5 days, processed 595,464,211 queries (1,350/sec. avg) :) Very nice! Fast and using a recent version. I really need to upgrade one of these days. I keep meaning to get my slaves up to 3.23.47 (and one of them to 4.0.1, just to kick the tires in production). Then I'll worry about the master. I finally did something about not having much in the way of visual stats: http://db.finance.yahoo.com/rrd/2002/01/2002-01-24.html It'll be interesting to see what the charts look like after I rig it up on the slaves. Since they get mostly updates, it should be quite different. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 22 days, processed 511,551,983 queries (265/sec. avg) - 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
