Website Run By Offsets Query

[Ben Binskin]

hi
Im just curious, im developing a web site for a client the content of
which is vehicles the database has a number of different methods to search
by, but as soon as you get to a singular vehicle you can browse to the next
/ previous vehicle in the database. To do this i use offsets.

My question is when i have a page that simply has a list of vehicles,
say the last 5 added for example, the url has to contain the offset value
for that vehicle. So far the only way i can determine to work out the
position of the vehicle in the recordset is to run a search through the
entire table, counting each entry, then once it hits the entry im after it
records the count (hence the offset value i need). Ive set up a function to
determine this for me. My only concern is that once i start displaying the
list of vehicles, hence running the function every time i want to display a
vehicle link, will this place an intolerable load on the server?

Is their an easier way to get the records position in the table? that will
reduce load on the server?

Cheers - Ben.

[René]

Really an off topic answer, but who cares.

Mostly you would not use your method but use "LIMIT " in their SQL to show
part or only one of the records. With for instance LIMIT 29, 1 you would
have the thirtiest row. The next would be just incrementing 29 and you don't
need any function to do that. It would be just one database query to get the
next, which I suppose would be the same as retrieving through a key value.
Most likely the response will take a bit longer that way.

Another way, more complicated, is having a field in your database that
points to the next and former record. That way you would retrieve through a
(indexed) key, which should speed up things. Of course you would need to
change everything around inserting and deleting records, because these
pointers would need to be updated and you would need to decide on what
criteria you decide what is the next record, which would be something like a
fixed sort order.

On my local Mysql server the query SELECT * FROM `whatever` LIMIT 130 , 1,
to somewhere in the middle of a table took 0.0079 seconds, and LIMIT 268 , 1
is to the end of this table and takes 0.0136 seconds. This is on a Athlon
600Mhz with one user; I am sure your situation could be faster.

When I do the query on a indexed key field value then the query to the
middle of the table takes 0.0065 seconds.

Conclusion: it really depends on the size of your table. If your table is
not extremely long, I would think the method with LIMIT is sufficiently fast
and will not load your server.

René
www.comunica2.net