>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
