How to optimize JOIN of large tables (3 million rows)?

Question

MySQL

Hey!
There is a "wide" (~25 fields) table with goods for 3 million rows.
And there is a table linking products to brands (~ 2.5 million rows). Relationship of tables is almost "one to one".
It is necessary to select brands of goods of a certain category.
The request takes 3-5 seconds:

SELECT ib.id_brand FROM item_tmp i 
JOIN item_brand ib ON ib.id_item=i.aid
WHERE i.enabled=1 
AND i.id_category BETWEEN 10107000 AND 10107999
GROUP BY ib.id_brand
ORDER BY null

Explain this query:

Table structure:

CREATE TABLE `item_tmp` (
 `aid` int(11) NOT NULL AUTO_INCREMENT,
 `id_category` int(11) NOT NULL,
 `enabled` tinyint(1) NOT NULL,
... 
и еще ~20 полей int и varchar 
... 
 PRIMARY KEY (`aid`),
 KEY `ce` (`id_category`,`enabled`) USING BTREE,
 KEY `iec` (`aid`,`enabled`,`id_category`) USING BTREE,
 KEY `cei` (`id_category`,`enabled`,`aid`) USING BTREE,
 KEY `c` (`id_category`) USING BTREE
) ENGINE=InnoDB

CREATE TABLE `item_brand` (
 `id_item` int(11) NOT NULL,
 `id_brand` mediumint(9) NOT NULL,
 KEY `ib` (`id_item`,`id_brand`)
) ENGINE=InnoDB

Server - SDD, 4GB RAM, 2 cores.
Does it make sense to divide the product table into two:
1. several main int-fields (using them to make joins and complex selections)
2. and the remaining 20 text fields (used only for output to the browser)
Can creating a temporary table (create temporary table) help? )?
Do I need to use foreign keys? (I don't know much about them).
I will be grateful for advice.

Answer 1

For good, brands are dragged into the goods, and the request is without join. To select unwind + distinct for brands, but your mysql can't do that.
If you get confused, then you can make "indexes in memory" for product ids (12Mb if an array) and for categories (btree), and keep up to date, then it will work in a matter of ms.

Answer 2

If brand names (or other parameters) do not participate in the selection, then you can not join at all.
The technology is simple in essence. You pull the desired products with the first request, then you form a list of unique brand IDs for these products.
Next, in the list of IDs, you drag the brands themselves with the second request:

SELECT `brand_id`, `brand_name`
FROM `brands`
WHERE `brand_id` IN (1,3,4,7);

and display them on the page like this:

$brand_id = $product['brand_id'];
if (isset($brand[$brand_id])) { // $brand - массив брендов [id][title] из второго запроса
    echo htmlspecialchars($brand[$brand_id]['brand_name']);
}

If there are not many brands (a couple of hundred), you can pull them all with one request and cache them in the desired form.