>Description: The update command seems does not using the index to find the rows to update. Here are the command I have execute on MySQL client. You will be found the different in execute time used. I don't know it is normal or just a bug of the MySQL server.
################################################### Rows matched: 6541 Changed: 1400 Warnings: 6541 ld_topic use index(FID_2) set page=1 where FID=1 order by replytime asc limit 40; Query OK, 40 rows affected (6.81 sec) Rows matched: 80 Changed: 40 Warnings: 0 mysql> update old_topic use index(FID_2) set page=1 where FID=1 and page='' order by replytime asc limit 40; Query OK, 40 rows affected (6.32 sec) Rows matched: 40 Changed: 40 Warnings: 0 mysql> update old_topic set page="" where FID=1; Query OK, 1400 rows affected (0.13 sec) Rows matched: 6541 Changed: 1400 Warnings: 6541 mysql> explain select * from old_topic use index(FID_2) where FID=1 and page='' order by replytime asc limit 40; +-----------+------+---------------+-------+---------+-------+------+-------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-----------+------+---------------+-------+---------+-------+------+-------------+ | old_topic | ref | FID_2 | FID_2 | 2 | const | 6291 | Using where | +-----------+------+---------------+-------+---------+-------+------+-------------+ 1 row in set (0.00 sec) mysql> show create table old_topic; CREATE TABLE `old_topic` ( `FID` smallint(5) unsigned NOT NULL default '0', `page` smallint(5) unsigned NOT NULL default '0', `MGID` mediumint(8) unsigned NOT NULL auto_increment, `status` enum('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20') NOT NULL default '1', `clicks` smallint(5) unsigned NOT NULL default '0', `topic` char(80) NOT NULL default '', `nickname` char(20) binary NOT NULL default '', `uid` mediumint(8) unsigned NOT NULL default '0', `no_of_reply` smallint(5) unsigned NOT NULL default '0', `last_replyer` char(20) binary NOT NULL default '', `replytime` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`MGID`), KEY `uid` (`uid`), KEY `FID` (`FID`,`page`), KEY `FID_2` (`FID`,`replytime`), FULLTEXT KEY `topic` (`topic`) ) TYPE=MyISAM ################################################### >How-To-Repeat: >Fix: >Submitter-Id: [EMAIL PROTECTED] >Originator: root >Organization: >MySQL support: [none | licence | email support | extended email support ] >Synopsis: update bug >Severity: >Priority: >Category: mysql >Class: >Release: mysql-4.0.11-gamma (Official MySQL RPM) >C compiler: 2.95.3 >C++ compiler: 2.95.3 >Environment: System: Linux ip-83-99-134-202.rev.dyxnet.com 2.4.18-14smp #1 SMP Wed Sep 4 12:34:47 EDT 2002 i686 i686 i386 GNU/Linux Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/3.2/specs Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking --host=i386-redhat-linux --with-system-zlib --enable-__cxa_atexit Thread model: posix gcc version 3.2 20020903 (Red Hat Linux 8.0 3.2-7) Compilation info: CC='gcc' CFLAGS='-O6 -fno-omit-frame-pointer -mpentium' CXX='g++' CXXFLAGS='-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx 1 root root 14 Jan 23 11:25 /lib/libc.so.6 -> libc-2.2.93.so -rwxr-xr-x 1 root root 1235468 Sep 6 07:12 /lib/libc-2.2.93.so -rw-r--r-- 1 root root 2233342 Sep 6 06:59 /usr/lib/libc.a -rw-r--r-- 1 root root 178 Sep 6 06:50 /usr/lib/libc.so Configure command: ./configure '--disable-shared' '--with-mysqld-ldflags=-all-static' '--with-client-ldflags=-all-static' '--without-berkeley-db' '--with-innodb' '--without-vio' '--without-openssl' '--enable-assembler' '--enable-local-infile' '--with-mysqld-user=mysql' '--with-unix-socket-path=/var/lib/mysql/mysql.sock' '--prefix=/' '--with-extra-charsets=complex' '--exec-prefix=/usr' '--libexecdir=/usr/sbin' '--sysconfdir=/etc' '--datadir=/usr/share' '--localstatedir=/var/lib/mysql' '--infodir=/usr/share/info' '--includedir=/usr/include' '--mandir=/usr/share/man' '--with-embedded-server' '--enable-thread-safe-client' '--with-comment=Official MySQL RPM' 'CFLAGS=-O6 -fno-omit-frame-pointer -mpentium' 'CXXFLAGS=-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' --------------------------------------------------------------------- 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