JOIN на большой таблицы — как ускорить?

Question

[Александр]

Привет!

Есть таблица товаров (item), таблица брендов (brand) и таблица соответствия товар=бренд (item_brand).
Товаров - 1.5 млн, брендов - 50 тыс.

Сервер: 2 ядра, 4Гб ОЗУ.

Для страницы "фильтра по брендам" делаю такой запрос (на всех полях участвующих в запросе проставил обычные индексы):

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

Один только этот запрос отрабатывает порядка 4-9 секунд. А подобных фильтров на странице еще 5-6. Страница грузится неимоверно долго первый раз (следующие обновления страницы мгновенны из-за кеширования).

Explain:

Подозреваю что или запросы не правильно строю, или индексы не отрабатывают.
Подскажите, в какую сторону копать?

Вот так фильтр выглядит:

Спасибо!

Answer 1

[RoverWhite]

У вас один item может иметь отношение с несколькими brand? Если нет тогда линк на бренд в таблицу Item перенести и отказаться от вспомогательной таблицы.
Далее какие индексы на таблице Item присутствуют?
Какой размер у индексов, какие поля Вы включили в индекс?
Если там помимо этого еще хватает таблиц то кеш индексов может переполняться и закешированный с диска индекс вылетит из памяти, и будет при таком обращении перечитываться с диска, а тут уже зависит от скорости диска и размера индекса.
Сделайте EXPLAIN SELECT brand.*, count(*) FROM brand .... на Ваш запрос, посмотрите что происходит....

Comments

[Александр]

1. item имеет только один brand, да, его можно перенести в таблицу. Но на странице есть и другие подобные фильтры "один ко многим" (фильтр по цвету, по размеру).

2. На таблице Item присутствуют обычные неуникальные индексы (id_category, enabled,...). Каждый индекс на отдельное поле.

3. Сделал Explain: puu.sh/sdL2y/15f49f17a4.png

[RoverWhite]

SHOW CREATE TABLE item;
SHOW CREATE TABLE item_brand;
SHOW CREATE TABLE brand;
Покажите пожалуйста

[Александр]

RoverWhite: puu.sh/sdUUx/5df626aa93.png сделал.

[Александр]

RoverWhite: Поясню, на item.id можно не обращать внимание, там хранится всякий "мусор", не участвующий в запросах. Связи идут через уникальное поле item.aid

[Александр]

RoverWhite: сейчас поэкспериментировал, даже одиночный запрос из item: SELECT * FROM item WHERE enabled=1 AND id_category IN (6,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,203,288,308,309) - выполняется 6 секунд. Это нормально, как вы считаете?

[Александр]

RoverWhite: Кажется понимаю ошибки в проектировании данных, нужно мне вложенность категорий товаров переделать, чтобы подкатегории выбирать простым ...id_category BETWEEN 2000 AND 2999... и избавиться от медленных IN (...). Думаю часть проблемы с производительностью это решит. Верно?

Answer 2

[Rsa97]

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

Comments

[Александр]

У вас небольшая опечатка (AS `brand` => AS `id_brand`). Но все равно, ваш запрос у меня еще дольше выполнялся, 13 секунд.