>Description:
Selecting for a number in a char table is REALLY slow.
>How-To-Repeat:
Large table, main index on column 'id' varchar(100).
mysql> select id from ticketid where id = '15473';
Empty set (0.00 sec)
mysql> select id from ticketid where id = 15473;
+--------------------------+
| id |
+--------------------------+
| [EMAIL PROTECTED] |
| [EMAIL PROTECTED] |
| [EMAIL PROTECTED] |
+--------------------------+
3 rows in set (3 min 49.81 sec)
mysql> select id from ticketid where id = 15473 and id like '15473%';
+--------------------------+
| id |
+--------------------------+
| [EMAIL PROTECTED] |
| [EMAIL PROTECTED] |
| [EMAIL PROTECTED] |
+--------------------------+
3 rows in set (4 min 6.83 sec)
Say what ???
mysql> explain select id,ticket,seq from ticketid where id = 15473 and id like
'15473%';
+----------+------+---------------+------+---------+------+--------+----------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+----------+------+---------------+------+---------+------+--------+----------+
| ticketid | ALL | PRIMARY | NULL | NULL | NULL | 406867 | where used
+----------+------+---------------+------+---------+------+--------+----------+
1 row in set (0.09 sec)
mysql> explain select id,ticket,seq from ticketid where id like '15473%';
+----------+-------+---------------+---------+---------+------+------+--------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+----------+-------+---------------+---------+---------+------+------+--------+
| ticketid | range | PRIMARY | PRIMARY | 100 | NULL | 2 | where used
+----------+-------+---------------+---------+---------+------+------+--------+
1 row in set (0.06 sec)
>Fix:
Does the SQL standard really require that you go through the table
and evaluate its contents numerically?
The 'like' operator should be able to use the index. Why doesn't it?
>Submitter-Id: <submitter ID>
>Originator: Matthias Urlichs
>Organization:
noris network AG
>MySQL support: licence
>Synopsis: Bad use of index
>Severity: serious
>Priority: medium
>Category: mysql
>Class: sw-bug
>Release: mysql-3.23.34 (noris network MySQL)
>Environment:
System: Linux play.smurf.noris.de 2.4.0s-noris-t5-2 #58 SMP Wed Jul 19 10:24:19 CEST
2000 i686 unknown
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/i686-pc-linux-gnu/2.95.3/specs
gcc version 2.95.3 19991030 (prerelease)
Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='-DTHREAD_SAFE_CLIENT
-felide-constructors -fno-exceptions -fno-rtti' LDFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 Jun 8 2000 /lib/libc.so.6 -> libc-2.1.3.so
-rwxr-xr-x 1 root root 4118299 Sep 20 1999 /lib/libc-2.1.2.so
-rwxr-xr-x 1 root root 4123003 Jun 8 2000 /lib/libc-2.1.3.so
-rw-r--r-- 1 root root 19203634 Jun 8 2000 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Jun 8 2000 /usr/lib/libc.so
-rwxr-xr-x 1 root root 2042654 Oct 27 17:09 /usr/lib/libc-client.a
Configure command: ./configure --prefix=/usr --with-debug --enable-shared
--without-mit-threads --libexecdir=/usr/sbin --localstatedir=/var/mysql
--enable-thread-safe-client --sysconfdir=/etc --datadir=/usr/share
--enable-large-files --without-readline --with-mysqld-user=mysql
--with-unix-socket-path=/var/run/mysql.socket --enable-strcoll '--with-comment=noris
network MySQL' --with-docs --with-bench --without-berkeley-db --without-bench
---------------------------------------------------------------------
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
