SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot
    Join Date
    May 2003
    Location
    Sarasota, FL
    Posts
    196
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Hello,

    I have a table structure like so:

    Code:
    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
    Code:
    SELECT * FROM photos WHERE is_active=1 AND review=0 AND category={some category GUID} ORDER BY date_added DESC
    .
    Chris Bloom
    Web Application Developer

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by xangelusx View Post
    ...query this table to find the next and previous consecutive records given a specific ID.
    Code:
    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}
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    May 2003
    Location
    Sarasota, FL
    Posts
    196
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!!
    Chris Bloom
    Web Application Developer

  4. #4
    SitePoint Zealot
    Join Date
    May 2003
    Location
    Sarasota, FL
    Posts
    196
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!
    Chris Bloom
    Web Application Developer


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •