>Description:
I am trying to get the next record given a key (numeric or string),
What is the best way to do this. I am currently using:
SELECT * from wk004.slsf01 where ___RecNo > 3000 limit 1;
table type possible_keys key key_len ref rows Extra
slsf01 range PRIMARY PRIMARY 4 NULL 349 where used
The explain result for this command show's rows accessed as 349,
there are 3349 records in the file. If I use:
SELECT * from wk004.slsf01 where ___RecNo > 0 limit 1;
explain shows 3349 rows.
table type possible_keys key key_len ref rows Extra
slsf01 range PRIMARY PRIMARY 4 NULL 3349 where used
To me this indicates that the more records in the file the slower
this statement will be. Timing results with 3000 vs 100000 records
verify this.
Why is rows not 1 in this case?
Is there a different SELECT command I should be using?
___RecNo is an AUTO-INCREMENT BIGINT field, and is the only member of the
PRIMARY key, sorted ASCENDING.
The problem occurs on any index I use (string or INT), single or multiple
keys.
Occurs on Linux and SCO Unix.
>How-To-Repeat:
EXPLAIN SELECT * from wk004.slsf01 where ___RecNo > 3000 limit 1;
EXPLAIN SELECT * from wk004.slsf01 where ___RecNo > 0 limit 1;
>Fix:
>Submitter-Id: jwythe
>Originator: jww
>Organization: Silk Systems Inc.
>MySQL support: none
>Synopsis: SELECT using > and limit not optimized.
>Severity: serious
>Priority: medium
>Category: mysql
>Class: support
>Release: mysql-3.23.37 (Source distribution)
>Server: /usr/local/bin/mysqladmin Ver 8.19 Distrib 3.23.37, for pc-sco3.2v5.0.4 on
>i386
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 3.23.37
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 11 min 1 sec
Threads: 1 Questions: 1 Slow queries: 0 Opens: 5 Flush tables: 1 Open tables: 0
Queries per second avg: 0.002
>Environment:
Touch Intel PII-300Mhz, Redhat 6.2 Linux/SCO Openserver 5, same
System: SCO_SV sysdev 3.2 2 i386
Some paths: /bin/make /usr/local/bin/gcc /bin/cc
GCC: Reading specs from /usr/local/lib/gcc-lib/i386-pc-sco3.2v5.0.5/2.95.2/specs
gcc version 2.95.2 19991024 (release)
Compilation info: CC='gcc' CFLAGS=' ' CXX='c++' CXXFLAGS='' LDFLAGS=''
LIBC:
lrwxrwxrwx 1 root sys 36 May 19 1998 /lib/libc.a ->
/opt/K/SCO/unixds/5.1.0Ha/lib/libc.a
lrwxrwxrwx 1 root sys 37 May 19 1998 /lib/libc.so ->
/opt/K/SCO/unixds/5.1.0Ha/lib/libc.so
lrwxrwxrwx 1 root sys 40 May 19 1998 /usr/lib/libc.a ->
/opt/K/SCO/unixds/5.1.0Ha/usr/lib/libc.a
lrwxrwxrwx 1 root sys 41 May 19 1998 /usr/lib/libc.so ->
/opt/K/SCO/unixds/5.1.0Ha/usr/lib/libc.so
lrwxrwxrwx 1 root root 41 May 13 1998 /usr/lib/libc.so.1 ->
/opt/K/SCO/Unix/5.0.4Eb/usr/lib/libc.so.1
Configure command: ./configure --with-low-memory
---------------------------------------------------------------------
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
