[thelist] php/mysql get next two records

[Ken Schaefer]

: -----Original Message-----
: From: thelist-bounces at lists.evolt.org [mailto:thelist-
: bounces at lists.evolt.org] On Behalf Of Burhan Khalid
: Sent: Tuesday, 8 February 2005 5:41 PM
: To: 'Mark Groen'; thelist at lists.evolt.org
: Subject: RE: [thelist] php/mysql get next two records
: 
: > > No, but it does support a "LIMIT" clause which does the
: > same in it's
: > > simplest syntax:
: > >
: > > SELECT
: > >         <whatever fields>
: > > FROM
: > >         myTable
: > > WHERE
: > >         myField => myCriteria
: > > ORDER BY
: > >         myField
: > > LIMIT 3
: > >
: > > (It does have to be specified after the ORDER clause.)
: > >
: > > For more details:
: > > http://dev.mysql.com/doc/mysql/en/select.html
: >
: > I think I will try Burhan's idea, the records may need a wee
: > bit of extra sorting and pulling them all out for one day in
: > a chunk would work for that, (there isn't something on every
: > hour, lots of blank spots), thanks everyone!
: >
: > I rather do like the sql way though, more elegant...hmmm
: 
: You know, even I like the SQL approach. However, I don't know in the long
: run if its efficient.  I mean, is it better to have one query (one hit to
: the database), then do the sorting, etc. in PHP (or whatever your poison)
: --
: than having a query to the database on each condition (so if you wanted
: four
: days, LIMIT 4, five days, LIMIT 5, etc.) I was always under the impression
: that database access was one of the slowest parts of an application; that
: it can be a good cause for a bottleneck.

There are some things that a modern DBMS is very, very good at. For example,
if you have indexed column, and the data in the column has a wide
distribution of values, then finding a specific record in the DB (or even
sorting the entire data set) will be much faster in the DBMS than pulling the
data out and sorting in some kind of middle tier.

Where databases start to fall down in is the type of things that you can't do
easily in SQL. If you want to start manipulating strings, or
extracting/searching on parts of a field (negating the benefits of
traditional indexes), then you may be better of doing this in your middle
tier. Just be aware that pulling large numbers of records from a DBMS incurs
a cost - a disk read cost, cross-process marshalling cost, and perhaps the
cost of pulling extraneous un-needed data across the wire. Furthermore,
sorting algorithms in your middle tier might not be computationally cheap
(since you have no predefined indexes), and so your scalability goes down the
drain.

What a lot of people get confused about is speed -vs- scalability. Just
because something is fast, doesn't mean it's scalable. Likewise, you mention
that database access is one of the slowest parts of an application, but that
doesn't mean that this puts a limit on your scalability of your app.

I'm not aware of how mySQL works internally. But if this was SQL Server (a
product I'm much more familiar with), I would use TOP xx in my sproc.

Cheers
Ken