How to optimize a query for 700,000+ records?

Question

MySQL

mysql, phpmyadmin, laravel.

$articles = DB::table('articles')->select('articles.*','folders.path','folders.title as parent_title')
        ->join('folders','folders.id','=','articles.parent_folder_id')
        ->orderBy('articles.sticked', 'DESC')
        ->orderby('published_at', 'DESC')

         ->limit(20)
         ->get();
// это весь запрос

And this is already redone, it was even bigger. Indexes in the database are added on sticked, published_at in the articles table.
The folder title is needed to form the URL of the article. I think I can add a column in the articles table with a ready-made URL, so you don't have to join it every time you select it. Or split into several small subqueries.
The number of articles is hundreds of thousands, there are also quite a few folders.

The SQL itself of the picture above:

EXPLAIN 
select `slc_articles`.*, `slc_folders`.`path`, `slc_folders`.`title`as `parent_title` from `slc_articles` 
inner join `slc_folders` on `slc_folders`.`id` = `slc_articles`.`parent_folder_id` 
order by `slc_articles`.`sticked` desc, `published_at` desc 
limit 11 
offset 0

This request is processed in 2.5-4.5 seconds, how can I optimize to 0.5-1.5?

Answer 1

the composite index with the necessary sorting is necessary. Well, in general, the stickiness index is a complicated thing. it's better to make two dates (one to display and one to sort/display sticky on top). Well, or store the sticky id in a separate place and get a list by id + the rest - from those sorted by date with the exception of sticky id. In any case, experiments are needed.
Well, at any cost, remove join from the query, perhaps at the cost of two queries or wrapping the main select in a subquery ( select ... from (select ... order by ... limit 10 ) as subquery inner join ... );

Answer 2

Well, there are 2 variants, as for me
1) Pull out all folders with one request, make its ID a key, cache it, and then go through everything calmly during the cycle. Also, do not forget about updating the cache when updating or inserting new data.
Sometimes logic works faster in PHP than in pure SQL
2) There is also a variant to make another table where you combine all the data of two tables, so to speak, simplify. Also you will make indexes necessary to you. Well, the same problem with updating the data, when updating / inserting, update your new table.
See what else you can cache in the app.

Answer 3

And indexes on Join?

Answer 4

Here index try to add articles.parent_folder_id