Как оптимизировать JOIN больших таблиц (3млн строк)?

Question

[Александр]

Привет!
Есть "широкая" (~25 полей) таблица с товарами на 3 млн строк.
И есть таблица с привязкой товаров к брендами (~ 2,5 млн строк). Связь таблиц почти "один к одному".

Необходимо выбрать бренды товаров определенной категории.
Запрос выполняется 3-5 секунд:

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 этого запроса:

Структура таблиц:

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

Сервер - SDD, 4GB RAM, 2 ядра.

Есть ли смысл разделить таблицу товаров на две:
1. несколько основных int-полей (по ним делать джойны и сложные выборки)
2. и остальные 20 текстовых полей (используется только для вывода в браузер)

Может ли помочь создание временной таблицы (create temporary table)?
Нужно ли использовать внешние ключи? (мало о них знаю).

Буду благодарен за советы.

Comments

[Melkij]

Как распределены данные по enabled? Попробуйте индекс по enabled & id_category

[Макс]

оптимизация - это здорово, но я бы еще добавил памяти. Стоит это сейчас копейки, а эффект хороший.

[lega]

> Как распределены данные по enabled?

enabled false строки вообще лучше переместить в одтельную таблицу если они не используются, будет быстрее и памяти съэкономите,
так же индекс по bool не являетя эффективным, т.к. там всего 2 значения - 2 секции в btree, в итоге скорости может не добавиться, а память скушается.

Answer 1

[lega]

По хорошему, бренды втащить в товары, и запрос без join. Для выборки unwind + distinct на бренды, но ваш mysql такое не умеет.

Если заморочиться, то можно сделать "индексы в памяти" для ид товаров (12Мб если массив) и для категорий (btree), и поддерживать в актуальном состоянии, тогда будет за считанные мс работать.

Answer 2

[AlikDex]

Если названия брендов(или другие параметры) не учавствуют в выборке, то можно и не джойнить совсем.
Технология проста по сути. Тянешь нужные товары первым запросом, затем формируешь список уникальных айди брендов этих товаров.
Далее по списку айдишников вторым запросом тащишь сами бренды:

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

и выводишь их на странице как-то так:

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

Если брендов не много(пару сотен), можно их все дернуть одним запросом и закешировать в нужном виде.