Find Most Recent Entry for an Intersection of Categories

Question

I know there are a number of "How do I find the most recent record" questions out there, but none of them quite solved my particular problem: in MySQL, I'm trying to find the most recent record for an entry that's mapped to two different categories in the same table. There's essentially an ENTRIES table with a bunch of info, a CATEGORIES table (id, name) and a ENTRY_CATEGORIES table (entry_id, category_id). I need to find the most recent record that's mapped to two different categories. I've managed to do it, but only by essentially joining a derived table on itself and it feels like there's a cleaner way to do this. How can I better express the following mess:

SELECT doc.entry_id
FROM exp_category_posts doc 
INNER JOIN exp_category_posts fund ON doc.entry_id = fund.entry_id 
INNER JOIN exp_weblog_titles t ON doc.entry_id = t.entry_id
WHERE doc.cat_id = 408 
AND fund.cat_id = 548 
AND t.entry_date = (SELECT MAX(t.entry_date)
FROM exp_category_posts doc 
INNER JOIN exp_category_posts fund ON doc.entry_id = fund.entry_id 
INNER JOIN exp_weblog_titles t ON doc.entry_id = t.entry_id
WHERE doc.cat_id = 408 
AND fund.cat_id = 548)

It's a hard-coded example where 408 and 548 would normally be fields as well. This is an Expression Engine database, if you're curious.

Answer 1

You might try replacing

AND t.entry_date = (SELECT MAX(t.entry_date)
FROM exp_category_posts doc 
INNER JOIN exp_category_posts fund ON doc.entry_id = fund.entry_id 
INNER JOIN exp_weblog_titles t ON doc.entry_id = t.entry_id
WHERE doc.cat_id = 408 
AND fund.cat_id = 548)

with:

ORDER BY t.entry_date DESC
LIMIT 1

The optimizer will probably end up with a similar query in the end (that's not guaranteed, of course, but it's fairly likely), but the query is half as long. You'd have to run explain and profile a few select queries to see if it performs as well or better.