List:General Discussion« Previous MessageNext Message »
From:Gerald Clark Date:June 29 2001 1:44pm
Subject:Re: SELECT using > and limit not optimized.
View as plain text  
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;

root@stripped 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 <mysql-thread77976@stripped>
> To unsubscribe, e-mail
> <mysql-unsubscribe-gerald_clark=suppliersystems.com@stripped>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-- 
Gerald L. Clark
gerald_clark@stripped

Thread
SELECT using > and limit not optimized.root28 Jun
  • Re: SELECT using > and limit not optimized.Gerald Clark29 Jun
  • How do I restore a database backed up using mysqldump?Victor SpĂ„ng Arthursson2 Jul
Re: How do I restore a database backed up using mysqldump?Tyler Longren2 Jul