How to best get 3 prior image and 3 later image records in MySQL query? -

I will briefly explain it to complete with a functional perspective. I am working on an image gallery on the page Shows an image, I want the sidebar to show thumbnails of images uploaded by that user. At max, should be 6, 3, which were posted before the current main image, and 3 were posted after the main image. You can see it as a stream of images by the same user through which you can navigate. I believe Flickr has a similar thing.

Technically, in my image table there is an auto-iced ID, a user id and date-uploaded field among several other columns.

How do your advice implement this kind of query? Can I add it to a question? Is there any easier MySQL utilities that can offset and handle such?

PS: I do not want to create an additional "rank" column, because it makes deletion difficult. Also, using the autoincrement ID seems risky, I can change it later for a GUID. Finally, I definitely search for a query that works and scales.

I know that I ask for a lot, but it seems that it is simple?

The query may look like the following.
With a UserID + image_id index (possibly additional fields to cover the objectives), it should do relatively well.

  SELECT field1, field2, whichever is from myTable where userID = some_id - AND image_id & gt; Id_of_the_previously_first_image ORDER by image_id LIMIT 7;  

To help with scaling, you should consider using a larger LIMIT value and cache accordingly.

Edit (Answer the comments / questions):
United Index ... from many fields Make, note that

  create [UNIQUE] index UserId_Image_id_idx on myTable (UserId, image_ida [, field1 ...])  

Note that Alternative elements of this query are in brackets ([]). I think the unique obstacle would be a good thing, the additional "cover" field (field 1, ...) is probably the beneficiary, but depending on the "width" of such extra fields as well as the overall setup and usage pattern (Since [large] indexed INSERTs / UPDATEs / DELETE may want to limit the number and size of such indices.)

Such an index data such as "type" is neither numeric nor string . This is made only of personal data types, for example, if UserId is VARCHAR (10) and Image_id is INT, the resulting index will use these two types for the underlying search parameters, i.e.

... WHERE UserId = 'JohnDoe' and image_id> 12389

In other words, there is no need to combine these criteria into a key.

On image_id
When you say image_id, you mean joint user / image id, right?
No, I mean only image_id. I assume that there is a separate field in the table. User ID has taken care of other points of the WHERE clause, the basic question to write indicates that this field is automatically generated, and I believe that we can rely on this field for the intended purposes. Alternatively, we can rely on other areas such as timestamps when uploading the image.

In addition to this, a follow-up, whether [monotonically increasing] by Image_id or ordered by timestamp_off_upload, we want to use it for a DESC order, the latest "content" first Show.