[Fwd: SELECT using > and limit not optimized.]

[John Wythe/SCO : sysdev : root]

John Wythe/SCO:sysdev:root wrote:

> Thanks for the Reply Gerald.
>
> I tried your suggestion, but it does not make any difference.
>
> Should there not be an implied ORDER BY when using an index?
>
> John
>
> Gerald Clark wrote:
>
> > 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


---------------------------------------------------------------------
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