Оптимизация формирования страницы каталога web-магазина с множеством фильтров

В одном из разработанных мною движков сайтов требовалось оптимизировать процесс формирования страницы каталога интернет-магазина с фильтрами выбора товаров.

Cайт представляет из себя онлайн-каталог б/у бытовой техники сети оффлайн магазинов Украины. Все товары на сайте фильтруются по следующим критериям:

• Бренд (Samsung, LG, Philips ...).
• Группа и подгруппа (Автотовары: GPS-навигаторы, Автомагнитолы; Аудио-видео: DVD-плееры, MP4-плееры ...).
• Город и магазин в котором находится товар.

По-умолчанию, на странице каталога в центральной части показывается список групп и подгрупп товаров с указанием количества доступных товаров в каждой группе/подгруппе, а в правой колонке показывается список городов с указанием количества товаров в каждом городе.



При выборе одного или нескольких городов в центральной части показывается уточненный список групп товаров (с указанием количества товара) которые есть в продаже в магазинах выбранных городов. В колонке под каждым выбранным городом открывается список магазинов данного города, который позволяет посмотреть список товаров в конкретных магазинах.



Если выбрать группу товара то в правой колонке добавляется фильтр брендов.



Пример:
Посетитель сайта выбрал группу товаров «Мобильные телефоны» в которой в общей сложности на момент написания статьи 3002 товара. В данной группе посетителю отобразился общий список товаров группы а так же возможность дополнительно отфильтровать по подгруппам.

В правой колонке отобразилось 2 фильтра: по производителям и по городам/магазинам.



Далее посетитель выбрал следующие бренды: Nokia, Philips и Samsung. Сайт пересчитал количество доступных товаров в группе «Мобильные телефоны» с учетом выбранных производителей, определили список городов, в которых есть все товары выбранных производителей и посчитал количество товаров в каждом городе и магазине города, определил полный список брендов группы «Мобильные телефоны» и посчитал количество товаров каждого производителя и сгенерил посетителю страницу:



Далее посетитель решил посмотреть какие из этих товаров продаются в магазинах в которые ему удобно было бы зайти (магазины возле работы, дома, по пути следования с работы домой) и выбрал нужные ему магазины. Кроме этого посетитель выбрал фильтр «Показать только новые поступления». Сайт при генерации страницы сделал следующее:

1. Пересчитал количество доступных товаров в группе «Мобильные телефоны» с учетом выбранных производителей, магазинов и пометки «Новинка»;
2. определили список городов, в которых есть товары выбранных производителей с пометкой «новинка» и посчитал количество товаров в каждом городе и магазине города;
3. определил список брендов группы «Мобильные телефоны» которые представлены в выбранных магазинах и у которых стоит пометка «новинка» и посчитал количество товаров каждого производителя

… и сгенерил посетителю страницу.



Итак, что выбрал посетитель:

1. Группа товаров «Мобильные телефоны»
2. Бренды Nokia, Philips и Samsung
3. Выбрал интересующие города и магазины
4. Выбрал фильтр «показывать только новинки»

Сайт в ответ:

1. сгенерил список товаров по выбранным фильтрам
2. определил список доступных подгрупп товаров и посчитал количество товара в каждой подруппе с учетом выбранных остальных фильтров
3. определил список доступных производителей товаров и посчитал количество товара каждого производителя с учетом выбранных остальных фильтров
4. определил список доступных городов и магазинов и посчитал количество товара в каждом городе/магазине с учетом выбранных остальных фильтров

И при генерации всего этого выполняется всего 2 SQL запроса:

• Получаем нужную десятку товаров по выбранным фильтрам
• вторым запросом получаем все остальное

А теперь немного кода…

При генерации страницы я, в зависимости от выбранных пользователейм фильтров, определяю перечень необходимых полей и группировок для SQL-запроса и выполняю запрос следующего плана (для нашего примера):

SELECT город, магазин, производитель, группа, count(идентификатор_товара) AS items_count
FROM таблица_товаров
WHERE
товар_активен = 'yes'
AND группа_товара IN(идентификатор выбранной группы товаров и перечень идентификаторов всех подгрупп выбранной группы) 
AND новинка = 'yes'
GROUP BY город, магазин, производитель, группа;<code>В ответ я получаю общее количество товаров в базе данных в указанных группах в разрезе выбранных группировок. В нашем примере запрос вернул 96 записей:

<img src="http://i.piccy.info/i5/92/40/954092/Snymok.png" alt="image"/>

Далее средствами PHP я перебираю все строки и подсчитываю количество по нужным мне фильтрам учитывая либо не учитывая некоторые колонки в результате запроса. 

Правда я каждый раз из кэша подтягиваю полные списки брендов, магазинов и групп, а вывожу пользователю только те, которые необходимо в данный момент вывести.