List:General Discussion« Previous MessageNext Message »
From:Ashwin Kutty Date:October 18 2001 5:31pm
Subject:RE: MySQL 4.0 - Order By & Limit
View as plain text  
Point well taken.. The query is as follows:-

SELECT * FROM dddeli WHERE title LIKE "%$keyword%" ORDER BY title desc LIMIT $start,$end;

Where $keyword is the keyword to search by from the field title, $start
and $end are to dictate which page of the results, etc..

title is varchar(128)

and dddeli has 33 fields..

On Thu, 18 Oct 2001, Steve Meyers wrote:

> It would help if you posted the actual query and results you are getting, instead of
> showing "simulated" results.  You mention the query below in your first post, but you
> never give the actual results of it.
>  
> Steve Meyers
> 
> 
> > -----Original Message-----
> > From: Ashwin Kutty [mailto:akutty@stripped]
> > Sent: Thursday, October 18, 2001 5:58 AM
> > To: mysql@stripped
> > Subject: Re: MySQL 4.0 - Order By & Limit
> > 
> > 
> > 
> > I just saw the mistake I made in my original post.. I want to Order By
> > letter, but when I do that with the Limit clause the results are as
> > shown in the original post.. Anyone know what the problem could be?
> > 
> > 
> > Steve Meyers wrote:
> > 
> > >That is actually how it is supposed to work -- MySQL gets all 
> > the rows, sorts them, and then returns up to the limit.  What you 
> > suggest would make no sense in any application, as far as I can 
> > tell.  The problem with the SQL queries given in the original 
> > post is the the ordering is being done by #, not letter.  Of 
> > course it will be fairly haphazard.  If you want to sort by 
> > number, then letter, you will need to specify it in your query as 
> > such (ORDER BY #, letter).
> > > 
> > >Steve Meyers
> > >
> > >
> > >>-----Original Message-----
> > >>From: Fournier Jocelyn [Presence-PC] [mailto:joc@stripped]
> > >>Sent: Tuesday, October 16, 2001 1:31 PM
> > >>To: Ashwin.Kutty@stripped
> > >>Cc: mysql@stripped
> > >>Subject: Re: MySQL 4.0 - Order By & Limit
> > >>
> > >>
> > >>Hi,
> > >>
> > >>I don't see the interest of this kind of behaviour : the main benefit
> of
> > >>LIMIT is MySQL stops fetching rows as soon as the number of 
> > rows specified
> > >>in LIMIT is achieved.
> > >>The behaviour you describe requires that MySQL fetches all the 
> > rows, sorts
> > >>it, and then applies LIMIT. (so no speed optimization !).
> > >>For your example, just use the SQL query without LIMIT, and 
> > then get only
> > >>the rows that interest you :) (I assume you are using language like
> PHP,
> > >>C++, etc... to execute this query ?)
> > >>
> > >>Regards,
> > >>
> > >>Jocelyn Fournier
> > >>Presence-PC
> > >>
> > >>----- Original Message -----
> > >>From: "Ashwin Kutty" <Ashwin.Kutty@stripped>
> > >>Cc: <mysql@stripped>
> > >>Sent: Tuesday, October 16, 2001 8:48 PM
> > >>Subject: MySQL 4.0 - Order By & Limit
> > >>
> > >>
> > >>>Hi,
> > >>>
> > >>>I thought version 4.0 was supposed to do a Order By first and then
> a
> > >>>Limit after that on the results returned on a SELECT.. I have just
> > >>>installed the new version and I hit the same problem I was hitting
> > >>>before, i.e., the Limit seems to be executing first and then the
> Order
> > >>>By causing all the results to be haphazard when they display.. Now
> if I
> > >>>go for a wider set of results, i.e. increase the Limit, the 
> > results come
> > >>>a lot better then, i.e. more of them are ordered right, but 
> > then, again,
> > >>>it still skips a lot more.. As an example:-
> > >>>
> > >>>Results Set
> > >>># letter
> > >>>1 f
> > >>>1 a
> > >>>2 b
> > >>>1 c
> > >>>3 d
> > >>>1 e
> > >>>1 h
> > >>>1 g
> > >>>
> > >>>
> > >>>Limit 2
> > >>>
> > >>>Order By #
> > >>>
> > >>>1 a
> > >>>1 f
> > >>>
> > >>>Then when I go to the next set, I get
> > >>>
> > >>>1 c
> > >>>1 e
> > >>>
> > >>>Instead I would like it to be as the following:-
> > >>>First Set:-
> > >>>1 a
> > >>>1 c
> > >>>Second Set:-
> > >>>1 e
> > >>>1 f
> > >>>
> > >>>etc..
> > >>>
> > >>>Any ideas?
> > >>>
> > >>>These are a few specs of the server & stuff..
> > >>>Linux kernel 2.4.7-2 on a Redhat 8 server running Mysql 4.0.0-alpha
> > >>>The query I have is as follows:-
> > >>>SELECT * from tablename WHERE fieldname LIKE "%keyword%" ORDER BY
> > >>>fieldname DESC LIMIT 0,10;
> > >>>
> > >>>Thanks..
> > >>>
> > >>>
> >
> >>>---------------------------------------------------------------------
> > >>>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-thread88073@stripped>
> > >>>To unsubscribe, e-mail
> > >>>
> > >><mysql-unsubscribe-joc=presence-pc.com@stripped>
> > >>
> > >>>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
> <mysql-thread88080@stripped>
> > >>To unsubscribe, e-mail 
> >
> >><mysql-unsubscribe-steve-mysql-mainlist=spamaphobia.com@stripped>
> > >>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 <mysql-thread88333@stripped>
> To unsubscribe, e-mail <mysql-unsubscribe-Ashwin.Kutty=dal.ca@stripped>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 
> 

---
Ashwin
kutty..
Systems Administrator
Dalhousie University Libraries
(902) 494-2694

Thread
Re: MySQL 4.0 - Order By & LimitFournier Jocelyn [Presence-PC]16 Oct
Re: MySQL 4.0 - Order By & LimitAshwin Kutty18 Oct
  • RE: MySQL 4.0 - Order By & LimitSteve Meyers18 Oct
    • RE: MySQL 4.0 - Order By & LimitAshwin Kutty18 Oct
      • RE: MySQL 4.0 - Order By & LimitSteve Meyers19 Oct
Re: MySQL 4.0 releasedMichael Furgal18 Oct
  • Re: MySQL 4.0 releasedMichael Widenius19 Oct
RE: MySQL 4.0 releasedBritt Johnston19 Oct
  • Mysql server doesn't runMark Coldheart19 Oct
    • Re: Mysql server doesn't runBill Adams19 Oct
  • RE: MySQL 4.0 releasedMichael Widenius19 Oct
RE: MySQL 4.0 releasedMichael Widenius19 Oct
RE: MySQL 4.0 releasedRichard C. Tucker19 Oct