JOIN on a large table - how to speed up?

Question

MySQL

Hey!
There is a product table (item), a brand table (brand) and a product = brand correspondence table (item_brand).
Products - 1.5 million, brands - 50 thousand.
Server: 2 cores, 4GB of RAM.
For the "filter by brands" page, I make the following request (I put down the usual indexes on all fields participating in the request):

SELECT brand.*, count(*) FROM brand 
JOIN item_brand ON item_brand.id_brand = brand.id 
JOIN item ON item.id = item_brand.id_item 
AND item.enabled=1 
AND item.id_category IN (21,317,318) 
GROUP BY brand.id 
ORDER BY count(*) DESC

This request alone fulfills about 4-9 seconds. And there are 5-6 more similar filters on the page. The page takes an incredibly long time to load the first time (next page updates are instant due to caching).
Explain:

I suspect that either I am not building the queries correctly, or the indexes are not working out.
Tell me, in which direction to dig?
This is what the filter looks like:

Thank you!

Answer 1

Can one item be related to several brands? If not, then transfer the link to the brand to the Item table and abandon the auxiliary table.
Further, what indexes are present on the Item table?
What is the size of the indexes, what fields did you include in the index?
If there are still enough tables in addition to this, then the index cache can overflow and the index cached from the disk will fly out of memory, and will be re-read from the disk during this access, but here it already depends on the disk speed and index size.
Do an EXPLAIN SELECT brand.*, count(*) FROM brand .... on your query, see what happens....

Answer 2

SELECT `b`.`*`, `i`.`count`
  FROM (
    SELECT `ib`.`id_brand` AS `brand`,  COUNT(*) AS `count` 
      FROM `item_brand` AS `ib`
      JOIN `item` AS `i` ON `i`.`enabled` = 1 
        AND `i`.`id_category` IN (21,317,318) 
        AND `i`.`id` = `ib`.`id_item`
      GROUP BY `ib`.`id_brand`
  ) AS `i`
  JOIN `brand` AS `b` ON `b`.`id` = `i`.`id_brand`
  ORDER BY `i`.`count` DESC