What can you think of instead of this heavy request?

Question

MySQL

SELECT r.id, c.id, 
    /* тут идёт сложный подзапрос, но в контексте вопроса нам это не интересно */ 123 AS `count`
FROM `region` r
JOIN `category` c on 1=1

This code counts once every 15 minutes the number of entries in each region for each category, including the number of entries in subcategories. But not an essence - a problem in given join. He's heavy.
What are the tools in mysql to make the join of two tables as fast as possible? Those. cache somehow, etc.?
The table with regions does NOT change.
The category table changes.

Answer 1

we should try not to make hundreds of thousands of subqueries, but to “leftjoin” on the result of the calculation (one query) of the quantities in the desired partition. you can even try memory tables.
according to the topic - the muscle has one merge algorithm. in this case, it will read both tables at a time, but the cursor with the result of the merge simply may not fit in the buffer and recordings will begin on disk, brakes, hell and israel (
option B. when filling tables with records that are being counted, calculate the necessary counts .working
option.calculate on the fly the necessary counts and cache.for especially stubborn splitting options, warm up the cache.