Помощь - Поиск - Пользователи - Календарь
Полная версия этой страницы: Оптимизация запроса в best_sellers.php
osCommerce по-русски. Форум. > Дизайн и программирование > Базы данных, Easy Populate
rdva
SQL запрос в best_sellers.php сильно тормозит скорость загрузки страницы. Пытаюсь оптимизировать запрос (вариант отключения этого бокса не рассматриваем), все таблицы в базе предварительно оптимизированы (optimize table, analyze table).
Вот этот запрос (в SQL виде, на примере конкретной категории с Id = 37097):

Код
SELECT DISTINCT p.products_id, pd.products_name
FROM products p, products_description pd, products_to_categories p2c, categories c
WHERE p.products_status = '1'
AND p.products_ordered >0
AND p.products_id = pd.products_id
AND pd.language_id = '4'
AND p.products_id = p2c.products_id
AND p2c.categories_id = c.categories_id
AND '37097'
IN (
c.categories_id, c.parent_id
)
AND find_in_set( '0', products_hide_from_groups ) =0
AND find_in_set( '0', categories_hide_from_groups ) =0
ORDER BY p.products_ordered DESC , pd.products_name
LIMIT 10


Время выполнения на тестовом компьютере - 2.8 сек.

EXPLAIN выдает следующее:
Код
id       select_type       table       type       possible_keys       key       key_len       ref       rows       Extra
1     SIMPLE     p     ALL     PRIMARY     NULL     NULL     NULL     1191     Using where; Using temporary; Using filesort
1     SIMPLE     pd     eq_ref     PRIMARY     PRIMARY     4     mobileba.p.products_id     1     Using where
1     SIMPLE     p2c     ref     products_id,categories_id     products_id     4     mobileba.p.products_id     132     Distinct
1     SIMPLE     c     eq_ref     PRIMARY     PRIMARY     4     mobileba.p2c.categories_id     1     Using where; Distinct


Методом тыка выяснил, если запретить использование индексов для categories, то запрос выполняется на пару порядков быстрее - 0.09 сек:
Код
SELECT DISTINCT p.products_id, pd.products_name
FROM products p, products_description pd, products_to_categories p2c, categories c
IGNORE INDEX (PRIMARY )
WHERE p.products_status = '1'
AND p.products_ordered >0
AND p.products_id = pd.products_id
AND pd.language_id = '4'
AND p.products_id = p2c.products_id
AND p2c.categories_id = c.categories_id
AND 37097
IN (
c.categories_id, c.parent_id
)
AND find_in_set( '0', products_hide_from_groups ) =0
AND find_in_set( '0', categories_hide_from_groups ) =0
ORDER BY p.products_ordered DESC , pd.products_name
LIMIT 10


EXPLAIN:
Код
id       select_type       table       type       possible_keys       key       key_len       ref       rows       Extra
1      SIMPLE      c      ALL      NULL      NULL      NULL      NULL      37017      Using where; Using temporary; Using filesort
1     SIMPLE     p2c     ref     products_id,categories_id     categories_id     4     mobileba.c.categories_id     5     
1     SIMPLE     pd     eq_ref     PRIMARY     PRIMARY     4     mobileba.p2c.products_id     1     Using where
1     SIMPLE     p     eq_ref     PRIMARY     PRIMARY     4     mobileba.pd.products_id     1     Using where


Количество строк итоговой таблицы во втором случае больше, чем в первом, но запрос выполняется в разы быстрее! Почему?
rdva
Опять же методом тыка найдено решение. Запрос выполняется более оптимальным образом, что дает резкое сокращение времени выборки из базы:

В /includes/boxes/best_sellers.php найти
Код
if (isset($current_category_id) && ($current_category_id > 0)) {
    $best_sellers_query = tep_db_query("select distinct p.products_id, pd.products_name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c where p.products_status = '1' and p.products_ordered > 0 and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and '" . (int)$current_category_id . "' in (c.categories_id, c.parent_id) and find_in_set('".$customer_group_id."', products_hide_from_groups) = 0 and find_in_set('" . $customer_group_id . "', categories_hide_from_groups) = 0 order by p.products_ordered desc, pd.products_name limit " . MAX_DISPLAY_BESTSELLERS);

заменить на
Код
if (isset($current_category_id) && ($current_category_id > 0)) {
    $best_sellers_query = tep_db_query("select distinct p.products_id, pd.products_name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c where p.products_status = '1' and p.products_ordered > 0 and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and (c.categories_id =" . (int)$current_category_id . " OR c.parent_id=" . (int)$current_category_id . ") and find_in_set('".$customer_group_id."', products_hide_from_groups) = 0 and find_in_set('" . $customer_group_id . "', categories_hide_from_groups) = 0 order by p.products_ordered desc, pd.products_name limit " . MAX_DISPLAY_BESTSELLERS);


Создать индексы (если их нет) для соответствующих столбцов в таблицах(таблица.столбец):
products.products_id (PRIMARY)
products_description.products_id (PRIMARY)
categories.categories_id(PRIMARY)
categories.parent_id
products_to_categories.products_id
products_to_categories.categories_id

Сделать OPTIMIZE TABLE для всех таблиц в базе.
Для просмотра полной версии этой страницы, пожалуйста, пройдите по ссылке.