Slow SQL query on a MariaDB JOIN for one-to-many table with ORDER BY and LIMIT

I am using MariaDB 10.11 and I cannot seem to successfully optimize a one to many relationship query with order and limit. The articles table has around 400k rows and the article_tag table (which is a links table between a tag and an article_id) has around 500k rows. Both tables will continue to grow, each article can have up to 5 tags.

SELECT a.article_id, 
a.title,  
m.username, 
a.article, 
vc.view_count,
a.inactive_article_id
FROM articles a  
JOIN article_tag t
ON a.article_id = t.article_id 
JOIN article_view_count vc
ON vc.article_id = a.article_id
JOIN members m
ON m.member_id = a.author_id 
WHERE m.inactive_account_id = 0  
AND a.inactive_article_id = 0
AND t.tag_id = 5
ORDER BY a.date_modified DESC 
LIMIT 0, 12

Right now it's taking over a second to run the query.

I have tried every possible INDEX I can think of, plus STRAIGHT_JOINs and sub-queries, but nothing seems to speed up this query. It wants to do the following on the article_tag table:

Using index; Using temporary; Using filesort

Here is the full EXPLAIN:

| id   | select_type | table | type   | possible_keys                                       | key     | key_len | ref                         | rows  | Extra                                        |
+------+-------------+-------+--------+-----------------------------------------------------+---------+---------+-----------------------------+-------+----------------------------------------------+
|    1 | SIMPLE      | t     | ref    | PRIMARY,idx_article_id                              | PRIMARY | 4       | const                       | 50178 | Using index; Using temporary; Using filesort |
|    1 | SIMPLE      | a     | eq_ref | PRIMARY,aauthorid_feature_idx,inactive_modified_idx | PRIMARY | 4       | t.article_id                | 1     | Using where                                  |
|    1 | SIMPLE      | m     | eq_ref | PRIMARY                                             | PRIMARY | 4       | a.authorid                  | 1     | Using where                                  |
|    1 | SIMPLE      | vc    | eq_ref | PRIMARY                                             | PRIMARY | 4       | t.article_id                | 1     |                                              |
+------+-------------+-------+--------+-----------------------------------------------------+---------+---------+-----------------------------+-------+----------------------------------------------+

show index in articles;
+----------+------------+-----------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table    | Non_unique | Key_name                    | Seq_in_index | Column_name         | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+----------+------------+-----------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| articles |          0 | PRIMARY                     |            1 | article_id          | A         |      373095 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| articles |          1 | aauthorid_feature_idx       |            1 | authorid            | A         |       24873 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| articles |          1 | aauthorid_feature_idx       |            2 | can_feature         | A         |       37309 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| articles |          1 | inactive_modified_idx       |            1 | inactive_article_id | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| articles |          1 | inactive_modified_idx       |            2 | date_modified       | A         |      373095 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| articles |          1 | catid_modified_idx          |            1 | catid               | A         |          12 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| articles |          1 | catid_modified_idx          |            2 | date_modified       | A         |      373095 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| articles |          1 | art_inactive_catid_idx      |            1 | catid               | A         |          18 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| articles |          1 | art_inactive_catid_idx      |            2 | inactive_article_id | A         |          60 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| articles |          1 | articles_idx_date_modified  |            1 | date_modified       | A         |      373095 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| articles |          1 | articles_idx_date_published |            1 | date_published      | A         |      373095 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
+----------+------------+-----------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+

show index in article_tag;
+---------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table         | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+---------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| article_theme |          0 | PRIMARY        |            1 | tag_id      | A         |         830 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| article_theme |          0 | PRIMARY        |            2 | article_id  | A         |      401991 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| article_theme |          1 | idx_article_id |            1 | article_id  | A         |      401991 |     NULL | NULL   |      | BTREE      |         |               | NO      |
+---------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+

What I want it to do is filter in the article_tag table (by the tag_id) and then let the INDEX on a.date_modified handle the sort and limit. However, it doesn't want to use the index on a.date_modified, unless I use a straight_join (but then it wants to sort the entire articles table before filtering by tag).

What am I doing wrong? Surely I don't need to denormalise the date_modified into the article_tag table?

You're being slammed by this notorious query-performance antipattern.

SELECT 
... something complex ... 
ORDER BY something
LIMIT something

This has to create a complex result set, then sort it, then discard all but a tiny fraction of it. That takes time.

You may be able to optimize this with the following index.

ALTER TABLE articles ADD INDEX inactive_datemod
(inactive_article_id, date_modified);

It may be able to be scanned in order by the date so MariaDB doesn't have to sort all your results. If it works, great. If it doesn't, please write another question and show us the output of ANALYZE your query.