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

Question

Hi there,

I'll explain briefly what I want to accomplish from a functional perspective. I'm working on an image gallery. On the page that shows a single image, I want the sidebar to show thumbnails of images uploaded by the same user. At a maximum, there should be 6, 3 that were posted before the current main image, and 3 that were posted after the main image. You could see it as a stream of images by the same user through which you can navigate. I believe Flickr has a similar thing.

Technically, my image table has an autoincremented id, a user id and a date_uploaded field, amongst many other columns.

What would your advise be on how to implement such a query? Can I combine this in a single query? Are there any handy MySQL utilities that can deal with offsets and such?

PS: I prefer not to create an extra "rank" column, since that would make managing deletions difficult. Also, using the autoincrement id seems risky, I might change it for a GUID later on. Finally, I'm of course looking for a query that performs and scales.

I know I ask for a lot, but it seems simpler than it is?

Answer 1

The query could look like the following.
With a UserID+image_id index (and possibly additional fields for covering purposes), this should perform relatively well.

SELECT field1, field2, whatever
FROM myTable
WHERE UserID = some_id
  -- AND image_id > id_of_the_previously_first_image
ORDER BY image_id
LIMIT 7;

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

Edit (answering remarks/questions):
The combined index...
is made of several fields, specifically

CREATE [UNIQUE] INDEX UserId_Image_id_idx
  ON myTable (UserId, image_ida [, field1 ...] )

Note that optional elements of this query are in brackets ([]). I would assume the UNIQUE constraint would be a good thing. The additional "covering" fields (field1,...) maybe beneficiary, but would depend on the "width" of such additional fields as well as on the overall setup and usage patterns (since [large] indexes slow down INSERTs/UPDATEs/DELETEs, one may wish to limit the number and size of such indexes etc.)

Such an index data "type" is neither numeric nor string etc. It is simply made of the individual data types. For example if UserId is VARCHAR(10) and Image_id is INT, the resulting index would use these two types for the underlying search criteria, i.e.

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

in other words one needn't combine these criteria into a single key.

On image_id
*when you say image_id, you mean the combined user/image id, right?*
No, I mean only image_id. I'm assuming this field is a separate field in the table. The UserID is taken care of in the other predicate of the WHERE clause. The original question write up indicates that this field is auto-generated, and I'm assuming we can rely on this field for sorting purposes. Alternatively we could rely on other fields such as the timestamp when the image was uploaded and such.

Also, an afterthought, whether ordered by a [monotonically increasing] Image_id or by the Timestamp_of_upload, we may want to use a DESC order, to show the latest "stuff" first.