[EMAIL PROTECTED] writes:
> >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
>
>
Hi!
First of all, where have you got 3.23.34 when it is not out ??
Second what type of column is id and what does PRIMARY KEY consist
of.
Third --with-debug is slower then without.
Regards,
Sinisa
____ __ _____ _____ ___ == MySQL AB
/*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED]
/*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaca, Cyprus
/*/ /*/ /*/\*\_/*/ \*\_/*/ |*|____
^^^^^^^^^^^^/*/^^^^^^^^^^^\*\^^^^^^^^^^^
/*/ \*\ Developers Team
---------------------------------------------------------------------
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
