MySQL Slow JOIN query when using ORDERBY

Question

I have a problem with this query:

SELECT a.*
FROM smartressort AS s
JOIN smartressort_to_ressort AS str
    ON s.id = str.smartressort_id
JOIN article_to_ressort AS atr
    ON str.ressort_id = atr.ressort_id
JOIN article AS a FORCE INDEX (source_created)
    ON atr.article_id = a.id    
WHERE
    s.id = 1
ORDER BY
    a.created_at DESC
LIMIT 25;

This one is realy slow, it some times takes 14 sec.

EXPLAIN show this:

1   SIMPLE  s   const   PRIMARY PRIMARY 4   const   1   Using index; Using temporary; Using filesort
1   SIMPLE  str ref PRIMARY,ressort_id  PRIMARY 4   const   1   Using index
1   SIMPLE  atr ref PRIMARY,article_id  PRIMARY 4   com.nps.lvz-prod.str.ressort_id 1262    Using index
1   SIMPLE  a   ALL NULL    NULL    NULL    NULL    146677  Using where; Using join buffer (flat, BNL join)

so the last "all" type is realy bad. But i already tried to force using the index, with no luck.

The Article Table looks like this:

CREATE TABLE `article` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`node_id` varchar(255) NOT NULL DEFAULT '',
`object_id` varchar(255) DEFAULT NULL,
`headline_1` varchar(255) NOT NULL DEFAULT '',
`created_at` datetime(3) NOT NULL,
`updated_at` datetime(3) NOT NULL,
`teaser_text` longtext NOT NULL,
`content_text` longtext NOT NULL,
PRIMARY KEY (`id`),
KEY `article_nodeid` (`node_id`),
KEY `article_objectid` (`object_id`),
KEY `source_created` (`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=161116 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;

When i remove the FORCE INDEX, the Explain gets better, but the query is still slow.

Explain Without force index:

1   SIMPLE  s   const   PRIMARY PRIMARY 4   const   1   Using index; Using temporary; Using filesort
1   SIMPLE  str ref PRIMARY,ressort_id  PRIMARY 4   const   1   Using index
1   SIMPLE  atr ref PRIMARY,article_id  PRIMARY 4   com.nps.lvz-prod.str.ressort_id 1262    Using index
1   SIMPLE  a   eq_ref  PRIMARY PRIMARY 4   com.nps.lvz-prod.atr.article_id 1   

And for another smartressort id(3) it looks like this:

1   SIMPLE  s   const   PRIMARY PRIMARY 4   const   1   Using index; Using temporary; Using filesort
1   SIMPLE  str ref PRIMARY,ressort_id  PRIMARY 4   const   13  Using index
1   SIMPLE  atr ref PRIMARY,article_id  PRIMARY 4   com.nps.lvz-prod.str.ressort_id 1262    Using index
1   SIMPLE  a   eq_ref  PRIMARY PRIMARY 4   com.nps.lvz-prod.atr.article_id 1   

Here we have 13 Ressorts for one Smartressort. Rows: 1x1x13x1262x1 = 16.406

1) What can i do to make this request faster?

2) What's wrong with the source_created index?

Answer 1

The SELECT * you have in your query is ugly, and this can often be an index killer. It can preclude the use of an index, because most indices you would define would not cover every column demanded by the SELECT *. The approach of this answer is to index all other tables in your query, which would therefore incentivize MySQL to just do a single scan over the article table.

CREATE INDEX idx1 ON article_to_ressort (article_id, ressort_id);
CREATE INDEX idx2 ON smartressort_to_ressort (ressort_id, smartressort_id);

These two indices should speed up the joining process. Note that I did not define an index for the smartressort table, assuming that its id column is already a primary key. I would probably write your query starting with the article table, and joining outwards, but it should not really matter.

Also, forcing an index is mostly either a bad idea or not necessary. The optimizer can usually figure out when it is best to use an index.

Answer 2

SELECT many columns FROM tables ORDER BY something LIMIT few is a notorious performance antipattern; it has to retrieve and order a whole mess of rows and columns, just to discard all but a few rows of the result set.

The trick is to figure out which values of article.id you need in your result set, then retrieve just those values. It's called a deferred join.

This should get you that set of id values. There's probably no need to join the smartressort table because smartressort_to_ressort contains the id values you need.

                 SELECT a.id
                   FROM article a
                   JOIN article_to_ressort atr ON a.id = atr.article_id
                   JOIN smartressort_to_ressort str ON atr.ressort_id = str.ressort_id
                  WHERE str.smartressort_id = 1
                  ORDER BY a.created_at DESC
                  LIMIT 25

Then you can use this as a subquery to get the rows you need.

SELECT a.*
  FROM article a
 WHERE a.id IN (
                 SELECT a.id
                   FROM article a
                   JOIN article_to_ressort atr ON a.id = atr.article_id
                   JOIN smartressort_to_ressort str ON atr.ressort_id = str.ressort_id
                  WHERE str.smartressort_id = 1
                  ORDER BY a.created_at DESC
                  LIMIT 25
               )
 ORDER BY a.created_at DESC

The second ORDER BY makes sure the rows from article are in a predictable order. Your index optimization work, then, need only apply to the subquery.