По какому принципу делается связка ID категории - ID товара, если товаров и категорий много (в таблице 120 млн записей)? Товар может быть в нескольких категориях. Была идея привлечь патрициев, в принципе скорость немного увеличилась, но сделать нормальное партиционирование не удалось (пришлось делать PARTITION BY HASH), так как категорий у меня 40 тыс, а партиций MySQL максимум поддерживает 8192. Собственно проблема пока в COUNT(*) для фильтра на странице каталога товаров, чем больше товаров в категории, тем больше тупит COUNT(*), с небольшим количеством просто летает, а с 200 тыс товаров в категории уже жуёт секунд по 8. Индексы, избыточные индексы над primary key и прочие танцы с бубном пока ни к чему не привели. Может разбить по таблицам, одна категория = одна таблица? 40 тыс таблиц в базе насоздавать и джойнить затем в выборках необходимый раздел, хм...

miksoftst_st,

Обычную таблицу связку многие-ко-многим пробовали?
Как часто изменяются данные?
Откуда вообще так много товаров? Что за предметная область?
Фильтр в каталоге только по категориям или есть и другой?

Данные изменяются не часто (товары редко меняют принадлежность к категориям, но всё же иногда это случается), в основном это добавление (при добавлении новых товаров, в эту таблицу соответствий тоже идёт insert). Товаров не много пока, 20 млн, плюс 40 тыс категорий, вот в итоге примерно 120 млн и набежало в таблицу соответствий. Сама таблица из себя представляет ID_Товара, ID_Категории c уникальным констрейнтом по обоим полям и индексами на каждом из них (innodb). Фильтр есть по ценам и другим свойствам, но они берутся из других таблиц, в которых меньше записей и особо так не тормозят, то есть если их выкинуть, то подсчёт по категориям особо не убыстряется. На входе есть ID категорий (обычно не более 20-ти), по ним надо посчитать количество товаров с учётом фильтров по ценам, поиску и т.д. Запрос примерно набросал по памяти, первой идёт большая таблица (таблица связки) и второй джойнится более мелкая, с товарами и ограничениями выборки, что-то типа такого (SQL_NO_CACHE - это для тестирования) -

ProductID и CategoryID приходят со сторонних сервисов, первый - символьный (от 1 до 10-ти символов), второй bigint (в int не влазит). Констрейнт unique_prcat_index добавлен, чтобы незамусоривали таблицы (без них приходилось сидеть и вычищать дубликаты с таблиц). Вообще проблема одна - чем большее число возвращает COUNT(*), тем дольше тупит запрос. Если на одном разделе фильтр выдал count в 200 товаров, то запрос выполнится за 0.001 сек, а если на соседнем раздел выдаст 2 млн товаров, то запрос может висеть минут 20. Собственно даже простой SELECT COUNT(*) FROM table занимает 8 секунд. Здесь наверное только разбивать таблицы.

Ключи на тестовой таблице переделал, проверю что там поменялось с размером индексов и скоростью, innodb_buffer_pool_size выставлен по максимуму, но с ним проблема в другом - бд занимает примерно пол терабайта (с данными и индексами), а ОЗУ на сервере намного меньше. Попозже сделаю тестовый пример - сам запрос, explain, скорость выполнения и данные по обоим таблицам, может проблема окажется в чём-то другом.