First of all, there is no NEXT record without an ORDER BY.
LIMIT returns a subset of the selected rows of the result set.
The result set must be selected and ordered before it can be limited.
you probably want:
SELECT * from wk004.slsf01 where ___RecNo > 3000 order by ___RecNo limit 1;
[EMAIL PROTECTED] wrote:
>> 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
--
Gerald L. Clark
[EMAIL PROTECTED]
---------------------------------------------------------------------
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
