Good day, It should be pointed out that even with this syntax, the database will still scour all of the rows in the table. The only savings is that the database won't return all of these rows due to the limit statement.
It might be more efficient to use min and max to determine which row in the database is before and after the desired row, and then only fetch those rows. This can easily done with subselects, but since MySQL does not support them one will have to use multiple queries to piece it together. Of course, if your table is small then this is mostly irrelevant anyway. It might also be worthwhile to note that this code can be made into one query with the UNION statement. However, MySQL doesn't support that part of SQL either (although the documentation says that it's provided in version 4.X ). ============================ Darren Gamble Planner, Regional Services Shaw Cablesystems GP 630 - 3rd Avenue SW Calgary, Alberta, Canada T2P 4L4 (403) 781-4948 -----Original Message----- From: Fournier Jocelyn [Presence-PC] [mailto:[EMAIL PROTECTED]] Sent: Friday, February 22, 2002 1:24 PM To: Chris Boget; Raymond Gubala; Mark Lo; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: [PHP] Next and Preview Row Yes, but how to get ONLY the 3 records you need ? Because often in an application, you don't care about the other records. In this case AFAIK, there is no other solution than issuing at least two queries : SELECT * FROM table WHERE field<='ID00025' ORDER BY field DESC LIMIT 2 SELECT * FROM table WHERE field>'ID00025' ORDER BY field ASC LIMIT 1 ----- Original Message ----- From: "Chris Boget" <[EMAIL PROTECTED]> To: "Fournier Jocelyn [Presence-PC]" <[EMAIL PROTECTED]>; "Raymond Gubala" <[EMAIL PROTECTED]>; "Mark Lo" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, February 22, 2002 9:17 PM Subject: Re: [PHP] Next and Preview Row > > It seems worst to me because in your case mysql has to retrieve all the > > rows. If it's a table with 1 million records or more, this should hurt ;) > > As I said, it was pseudo code. Now, imagine that you were just > getting the records for a particular user? a particular application? > Where there won't be millions and millions of rows? > > Chris > > > --------------------------------------------------------------------- > 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 > > -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
