Re: FN-FORUM: Choosing the next record in MySQL DB

date posted 14th May 2008 12:37

Sorry i assumed folk would get the point with 'select *' could be
replaced with a select for the exact data you require but maybe not.

I like consolidating queries instead of firing off more queries for
data that already could already be retrieved.
I'm not exactly sure that this way is more complex form my point of
view and definitely not less maintainable.
Maintaining one query is always better than three.

I see your point Graham also, but your query seems to perform slower
than mine with the same results.

Mine - 0.125 secs
Yours - 0.234 secs



On Wed, May 14, 2008 at 1:25 PM, Graham Stark
[EMAIL REMOVED] wrote:
>
> On Wed, 2008-05-14 at 11:33 +0000, Dom Latter wrote:
> > On Wednesday 14 May 2008 12:31:38 Tom Wilson wrote:
> > > Try this,
> > >
> > > (select * from news where ID > > UNION
> > > (select * from news where ID >= $id order by ID ASC LIMIT 2)
> > >
> > > This returns the rows your after and the surrounding two rows either
> > > side, so if $id=8 it will return rows 8,7 and 9.
> >
> > If 8 is the first or last ID then you'll only get two rows and you'll have
> > lots of work sorting it out.
> >
> > Don't know why everyone wants to make this so complicated.
> >
> > Select what you need (not "select *") to display the page for the current ID.
> >
> > Select ID for next page, select ID for previous.
> >
> > It's simple, it's maintainable. *If* there's a performance issue, then start
> > getting clever with stored procedures, complicated queries, what have you:
> > but in that case I'd be at least considering denormalising and having the next
> > and previous IDs in the same row to keep the database server load to a
> > minimum.
> >
>
> I haven't been following this one, but given what I understand the
> problem to be, Tom's solution looks quite elegant. The problem is that
> there's no simple way to select the id for the next page since the ids
> are not contiguous (so you can't just say "select $id+1"): Tom solves
> that nicely. Another approach might be:
>
> select * from news where id = $id or id = (select max(id) from id1 where
> id < $id ) or id = (select min(id) from id1 where id > $id );
>
> The "select *" seems fair enough to me in this context since we don't
> actually know what's in the table, or which fields are wanted.
>
> Graham
>
>
>
>
>
>
> --
> Freelancers, contractors earn more with Prosperity4
> Call 0870 870 4414 or visit www.prosperity4.com
> and benefit from Inland Revenue approved expenses today.
>
> To advertise here: http://www.freelancers.net/advertising.html
>
>