Most efficient way to select "Next" and "Previous" record with non-numeric keys?

[xangelusx]

Hello,

I have a table structure like so:

CREATE TABLE `photos` (
  `id` varchar(40) NOT NULL,
  `date_added` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `date_updated` timestamp NULL default NULL,
  `is_active` tinyint(1) unsigned NOT NULL default '1',
  `review` tinyint(1) unsigned NOT NULL default '1',
  `account` varchar(40) NOT NULL,
  `vote_count` int(10) unsigned NOT NULL default '0',
  `vote_up` int(10) unsigned NOT NULL default '0',
  `category` varchar(40) NOT NULL,
  `file_img` varchar(40) NOT NULL,
  `file_original_img` varchar(40) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `review` (`review`),
  KEY `account` (`account`),
  KEY `is_active` (`is_active`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

The ID field is actually a 40-char GUID rather than a traditional numeric auto-inc. I am wondering what is the most efficient way to query this table to find the next and previous consecutive records given a specific ID.

The original parameters would be

SELECT * FROM photos WHERE is_active=1 AND review=0 AND category={some category GUID} ORDER BY date_added DESC

.

[r937]

...query this table to find the next and previous consecutive records given a specific ID.

SELECT date_added
     , date_updated
     , account
     , vote_count
     , vote_up
     , category
     , file_img
     , file_original_img
     , ( SELECT id
           FROM photos
          WHERE date_added =
                ( SELECT MAX(date_added)
                    FROM photos 
                   WHERE is_active = 1 
                     AND review = 0 
                     AND date_added < T.date_added ) ) AS prev             
     , ( SELECT id
           FROM photos
          WHERE date_added =
                ( SELECT MIN(date_added)
                    FROM photos 
                   WHERE is_active = 1 
                     AND review = 0 
                     AND date_added > T.date_added ) ) AS next             
  FROM photos AS T
 WHERE is_active = 1 
   AND review = 0 
   AND id = {a specific id}

[xangelusx]

Hmm, that looks like it might work. I am worried that it might skip over some photos though if a few go in at the same second (ok, unlikely but possible - the client expects this to be a high volume site) I'll plug it in and see if it works. Thanks!!

[xangelusx]

Just to follow up - I added a double precision float (18,8) column that would store a microtime value in the form of nnnnnnnnnn.nnnnnnnn, which I populate when the row is created. It was the easiest way to add a unique sortable column (date_added wasn't unique enough) without having to worry about table locking (to get the last orderable number, e.g. display_order). So adjusting your query to use that column instead gets the desired data. THANKS!!

PS: It was also easy enough to populate the new column in existing rows - concat the old date_added timestamp value with the current microsecond value and viola! It might put a few rows out of their natural order, but it's more precise than it would have been otherwise and no dupes!