Fetching a Single Row from Join Table

Asked 2011-05-12T19:10:09.263

Here are my tables:

CREATE TABLE `articles` (
    `id` int(10) unsigned not null auto_increment,
    `author_id` int(10) unsigned not null,
    `date_created` datetime not null,
    PRIMARY KEY(id)
) ENGINE=InnoDB;

CREATE TABLE `article_contents` (
    `article_id` int(10) unsigned not null,
    `title` varchar(100) not null,
    `content` text not null,
    PRIMARY KEY(article_id)
) ENGINE=InnoDB;

CREATE TABLE `article_images` (
    `article_id` int(10) unsigned not null,
    `filename` varchar(100) not null,
    `date_added` datetime not null,
    UNIQUE INDEX(article_id, filename)
) ENGINE=InnoDB;

Every article can have one or more images associated with it. I'd like to display the last 40 written articles on a page, along with the most recent image associated with the article. What I can't figure out is how to join with the article_images table, and only retrieve a single row.

Edit: It's important that the solution performs well. The solutions I've seen so far -- which use derived tables -- take a minute or more to complete.

mysql

Edit Report

3 Answers

This is a case where an inline subquery, rather than a join, will work well:

select articles.*,
       article_contents.title,
       article_contents.content,
       (select article_images.filename
       from article_images
       where article_images.article_id = articles.id
       order by article_images.date_added desc
       limit 1
       ) as image_filename
from articles
join article_contents
on article_contents.article_id = articles.id
order by articles.date_created desc
limit 40;

Performance wise, it will nestloop through the top 40 rows of articles, which is the fastest possible plan; and for each of these rows, the subquery will nestloop to the top applicable row in article_images, which also happens to be the fastest plan for a given article.

If you need to fetch more than a single field from the images table, I take it you've an image_id. Assuming so, grab the image_id instead, and then do a second query with an in clause to retrieve the rows you need.

An alternative (and slightly faster) approach will be to use triggers to keep the latest image_id stored in the articles table. Doing will allow you to left join the images directly.

answered 2011-05-16T12:05:19.720

SELECT a.id, ai.filename
    FROM articles a
        LEFT JOIN (SELECT article_id, MAX(date_added) AS MaxDate
                       FROM article_images 
                       GROUP BY article_id) maxai
                INNER JOIN article_images ai
                    ON maxai.article_id = ai.article_id
                        AND maxai.MaxDate = ai.date_added
            ON a.id = maxai.article_id
    ORDER BY a.date_created DESC
    LIMIT 40

answered 2011-05-12T19:20:27.993

Try something like this

SELECT  A.author_id ,
        Images.article_id ,
        Images.filename ,
        Images.date_added 
FROM    articles AS A
        LEFT JOIN ( SELECT  AI1.article_id ,
                            AI1.filename ,
                            AI1.date_added
                    FROM    article_images AS AI1
                    WHERE   AI1.date_added = ( SELECT   MAX(date_added)
                                               FROM     article_images AS AI2
                                               WHERE    AI2.article_id = AI1.article_id
                                             )
                  ) AS Images ON A.id = Images.article_id

You will also need to add an index to the article_images table

(article_id ASC, date_added ASC)

answered 2011-05-16T03:36:48.187

Fetching a Single Row from Join Table

3 Answers

Your Answer