Re: "Sending data", but no data sent?
Can anyone tell me how to UNSUBSCRIBE from this LIST. Thanks a lot "Matthias Urlichs" <[EMAIL PROTECTED]> wrote: > > Hi, > > Sinisa Milivojevic: > > First of all, where have you got 3.23.34 when it is not out ?? > > > It's the version in your current BitKeeper archive. > > > Second what type of column is id and what does PRIMARY KEY consist of. > > > >>> explain ticketid; > Field Type Null Key Default Extra > id varchar(100) PRI > ticket int(11) MUL 0 > sender int(11) MUL 0 > added int(11) YES > seq int(11) 0 > extern char(1) YES > inhalt mediumtext YES > typ smallint(6) YES > d_data int(11) YES > > >>> show index from ticketid; > Table Non_unique Key_name Seq_in_index Column_name Collation > ticketid 0 PRIMARY 1 id A > ticketid 1 ticket 1 ticket A > ticketid 1 ticket 2 seq A > ticketid 1 sender_i 1 sender A > > > Third --with-debug is slower then without. > > > But not that much slower... > > -- > Matthias Urlichs | noris network AG | http://smurf.noris.de/ > > - > 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 > > __ Get your own FREE, personal Netscape Webmail account today at http://webmail.netscape.com/ - 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: "Sending data", but no data sent?
Hi, Sinisa Milivojevic: > And what happens if you put quotes ??? ... id = '123'... > Then it works, of course. But the nice thing about the numeric select is that it also accepts values like 123x45 or 123-45-67 (but not 1234), which is why I wanted to use it. -- Matthias Urlichs | noris network AG | http://smurf.noris.de/ -- New York is the biggest boob town in America. All any of those hokum peddlers need to do in selling New Yorkers their phony` goods is to ask what they want, and they'll be sure to get it. -- Bat Masterson - 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: "Sending data", but no data sent?
Matthias Urlichs writes:
> Hi,
>
>
> select * from ticketid where id like "123%"
>
> correctly uses the above index, but a more restrictive query, i.e.
>
> select * from ticketid where id like "123%" and id = 123
>
> doesn't. This is _not_ the expected result.
>
> --
> Matthias Urlichs | noris network AG | http://smurf.noris.de/
> --
> The decent moderation of today will be the least human of things
> tomorrow. At the time of the Spanish Inquisition, the opinion of good
> sense and of the good medium was certainly that people ought not to burn
> too large a number of heretics; extreme and unreasonable opinion
> obviously demanded that they burn none at all.
> -- Maurice Maeterlinck
>
And what happens if you put quotes ??? ... id = '123'...
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
Re: "Sending data", but no data sent?
Hi, Sinisa Milivojevic: > > >>> explain ticketid; > > Field Type Null Key Default Extra > > id varchar(100) PRI > > > > >>> show index from ticketid; > > TableNon_unique Key_name Seq_in_index Column_name Collation > > ticketid 0 PRIMARY 1 id A > > After taking a look at your table, the answer to your question is > YES. varchar(100) is too large for index. > Ummm, I wasn't really asking a question, I was reporting the problem that select * from ticketid where id like "123%" correctly uses the above index, but a more restrictive query, i.e. select * from ticketid where id like "123%" and id = 123 doesn't. This is _not_ the expected result. -- Matthias Urlichs | noris network AG | http://smurf.noris.de/ -- The decent moderation of today will be the least human of things tomorrow. At the time of the Spanish Inquisition, the opinion of good sense and of the good medium was certainly that people ought not to burn too large a number of heretics; extreme and unreasonable opinion obviously demanded that they burn none at all. -- Maurice Maeterlinck - 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: "Sending data", but no data sent?
Matthias Urlichs writes:
> Hi,
>
> Sinisa Milivojevic:
> > First of all, where have you got 3.23.34 when it is not out ??
> >
> It's the version in your current BitKeeper archive.
>
> > Second what type of column is id and what does PRIMARY KEY consist of.
> >
> >>> explain ticketid;
> Field Type Null Key Default Extra
> id varchar(100) PRI
> ticket int(11) MUL 0
> sender int(11) MUL 0
> added int(11) YES
> seqint(11) 0
> extern char(1) YES
> inhalt mediumtext YES
> typsmallint(6) YES
> d_data int(11) YES
>
> >>> show index from ticketid;
> TableNon_unique Key_name Seq_in_index Column_name Collation
> ticketid 0 PRIMARY 1 id A
> ticketid 1 ticket 1 ticket A
> ticketid 1 ticket 2 seq A
> ticketid 1 sender_i1 sender A
>
> > Third --with-debug is slower then without.
> >
> But not that much slower...
>
> --
> Matthias Urlichs | noris network AG | http://smurf.noris.de/
>
After taking a look at your table, the answer to your question is
YES. varchar(100) is too large for index.
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
Re: "Sending data", but no data sent?
Hi, Sinisa Milivojevic: > First of all, where have you got 3.23.34 when it is not out ?? > It's the version in your current BitKeeper archive. > Second what type of column is id and what does PRIMARY KEY consist of. > >>> explain ticketid; Field Type Null Key Default Extra id varchar(100) PRI ticket int(11) MUL 0 sender int(11) MUL 0 added int(11) YES seqint(11) 0 extern char(1) YES inhalt mediumtext YES typsmallint(6) YES d_data int(11) YES >>> show index from ticketid; TableNon_unique Key_name Seq_in_index Column_name Collation ticketid 0 PRIMARY 1 id A ticketid 1 ticket 1 ticket A ticketid 1 ticket 2 seq A ticketid 1 sender_i1 sender A > Third --with-debug is slower then without. > But not that much slower... -- Matthias Urlichs | noris network AG | http://smurf.noris.de/ - 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: "Sending data", but no data sent?
[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:
> >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
