Whether it is possible to optimize the given request or to look towards denormalization?

Question

MySQL

This SQL query has various modifications in the code, but the essence is the same - it works to display ads, with information about the category and region.
For clarity, I will show the image:

Here is the request itself. There is nothing complicated in it, it is a JOIN on several tables and conditions in WHERE:

explain SELECT STRAIGHT_JOIN *
    FROM `advert`
    INNER JOIN `user-country` ON `advert`.`advert_place_country` = `user-country`.`id`
    INNER JOIN `user-region` ON `advert`.`advert_place_region` = `user-region`.`id`
    INNER JOIN `user-city` ON `advert`.`advert_place_city` = `user-city`.`id`
    INNER JOIN `user` ON `user`.`id` = `advert`.`advert_id_user`
    INNER JOIN `category` ON `advert`.`advert_category` = `category`.`id`
    WHERE
        `advert_active` = 1 
        AND `advert`.`advert_payment` = 1 
        AND `advert_place_country` = 3159 
        AND `user`.`user_active` = 1
    ORDER BY
        `advert`.`advert_create_date`
    DESC
LIMIT 0, 80

Here's explain:

I'm not satisfied with the request processing time of 0.2 - 0.3 seconds and the number of affected rows. The index is like this:

`findListForCatalog` (
    `advert_active`,
    `advert_payment`,
    `advert_place_country`,
    `advert_place_region`, // в данном запросе не используется 
    `advert_place_city`    // в данном запросе не используется
)

Now I'm starting to think about data denormalization and create triggers that will write textual information of regionality and category (Category name, City, region and country name) to the advert ad table. The table will grow many times, but I just don’t see any other way now, I’ve tried a bunch of options. There are only 20,000 records in the table, but the running time in 0.3 does not suit me at all.

Answer 1

8.2.1.15 ORDER BY Optimization

Answer 2

If indexes are affixed everywhere, then it should not work for so long. First, localize the problem - try to remove all the joins and see how long it will take to process without joins. If it's fast, then gradually add joins until the time becomes abnormally large, then you will understand where the plug is.
advert_create_date also index if not already

Answer 3

Can't you sort by id? Usually later orders have a larger id.
And without an index on the sort field, there will be a complete enumeration of the result

Answer 4

Why STRAIGHT_JOIN?
If there are only 20,000 records in the table, and the index chose 18049, is there any point in such a cumbersome composite index if you still had to go through almost the entire table anyway? It may well turn out that it will be faster without it, or leave only the main column, the most different, and the column for sorting.