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) MUL0 sender int(11) MUL0 added int(11) YES NULL seq int(11) 0 extern char(1) YES NULL inhalt mediumtext YES NULL typ smallint(6) YES NULL d_data int(11) YES NULL 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
Sending data, but no data sent?
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
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: 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
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 NULL seqint(11) 0 extern char(1) YES NULL inhalt mediumtext YES NULL typsmallint(6) YES NULL d_data int(11) YES NULL 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?
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 NULL
seqint(11) 0
extern char(1) YES NULL
inhalt mediumtext YES NULL
typsmallint(6) YES NULL
d_data int(11) YES NULL
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?
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: 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
