Get previous/next record by key

[Karma]

I'm trying to speed up the connection of a query but I'm having a few problems.

I need to fetch the previous/next record without reading through the whole table, but it needs to be based on a 2 column key.

8394 - Surname0, Firstname0
1234 - Surname1, Firstname1 <- previous
5673 - Surname2, Firstname2 <- Current record
2345 - Surname3, Firstname3 <- next
3451 - Surname4, Firstname4

At the moment, I'm reading in the whole table (1000s of records), moving to an array and doing it that way but this is really slowing things down, ie:

$queryGetUser = "SELECT * FROM $tableUsers WHERE display='Y' ORDER BY surname,firstname ASC";

$q=0;
while ($numberUsers > $q) 
{
 $userID[$q] = mysql_result($resultUsers,$q,"userid");
 $userSurname[$q] = mysql_result($resultUsers,$q,"surname");
 $userFirstname[$q] = mysql_result($resultUsers,$q,"firstname");
 $q++;
 }

$key = array_search($currentUserID, $userID);
$prevKey = $key - 1;
$nextKey = $key + 1;

$prevName = $userSurname[$prevKey] . ", " . $userFirstname[$prevKey];
$nextName = $userSurname[$nextKey ] . ", " . $userFirstname[$nextKey];

Is there an easier way of doing this using less resource?

[enigma1]

use "limit" to specify from where to begin fetching rows as well as how many.

eg:
ORDER BY surname,firstname limit 10,50";

you should be able to determine next/previous without fetching the entire table.

[cneeds]

Hi Karma

Make an SQL view of the table with an auto increment key column, surname and firstname sorted in the next two columns and userid in the last column for reference to the main table. You use the key exactly the same as you use it now except that you don't have to read the whole table to get it. mySQL does that for you.

Chris