I have three tables: categories, articles, and article\_events, with the following structure

```
categories: id, name                        (100,000 rows)
articles: id, category_id                   (6000 rows)
article_events: id, article_id, status_id   (20,000 rows)

```

The highest article\_events.id for each article row describes the current status of each article.

I'm returning a table of categories and how many articles are in them with a most-recent-event status\_id of '1'.

What I have so far works, but is fairly slow (10 seconds) with the size of my tables. Wondering if there's a way to make this faster. All the tables have proper indexes as far as I know.

```
SELECT c.id, 
       c.name, 
       SUM(CASE WHEN e.status_id = 1 THEN 1 ELSE 0 END) article_count
FROM categories c
LEFT JOIN articles a ON a.category_id = c.id
LEFT JOIN (
    SELECT article_id, MAX(id) event_id
    FROM article_events
    GROUP BY article_id
) most_recent ON most_recent.article_id = a.id
LEFT JOIN article_events e ON most_recent.event_id = e.id
GROUP BY c.id

```

Basically I have to join to the events table twice, since asking for the status\_id along with the MAX(id) just returns the first status\_id it finds, and not the one associated with the MAX(id) row.

Any way to make this better? or do I just have to live with 10 seconds? Thanks!

Edit:

Here's my EXPLAIN for the query:

```
ID | select_type | table          | type   | possible_keys | key         | key_len | ref                  | rows   | Extra 
---------------------------------------------------------------------------------------------------------------------------
1  | PRIMARY     | c              | index  | NULL          | PRIMARY     | 4       | NULL                 | 124044 | Using index; Using temporary; Using filesort
1  | PRIMARY     | a              | ref    | category_id   | category_id | 4       | c.id                 | 3      |
1  | PRIMARY     | <derived2>     | ALL    | NULL          | NULL        | NULL    | NULL                 | 6351   |
1  | PRIMARY     | e              | eq_ref | PRIMARY       | PRIMARY     | 4       | most_recent.event_id | 1      |
2  | DERIVED     | article_events | ALL    | NULL          | NULL        | NULL    | NULL                 | 19743  | Using temporary; Using filesort

```