[Решено] Оптимизация запроса (поиск товара)

Тема в разделе "Базы Данных", создана пользователем Den1a, 4 дек 2017.

Статус темы:
Закрыта.
  1. Den1a

    Den1a

    Регистрация:
    16 авг 2013
    Сообщения:
    55
    Симпатии:
    3
    Здравствуйте.
    Возможно ли оптимизировать данный запрос?
    # Query_time: 1.217743 Lock_time: 0.000186 Rows_sent: 0 Rows_examined: 93425

    Код:
    SELECT pd.product_id, pd.name, p.model, p.price, p.image
    FROM oc_product_description AS pd
    LEFT JOIN oc_product AS p ON p.product_id = pd.product_id
    
    WHERE  (LOWER(pd.name) LIKE "%Труба%" OR LOWER(p.model) LIKE "%Труба%")
    AND (LOWER(pd.name) LIKE "%азбес%" OR LOWER(p.model) LIKE "%азбес%")
    ORDER BY
    
    CASE WHEN pd.name LIKE "Труба%" THEN 100 ELSE 0 END +
    CASE WHEN pd.name LIKE "%Труба%" THEN 50 ELSE 0 END
    DESC, LOWER(pd.name) ASC LIMIT 10;
     
  2. Master2KAZ

    Master2KAZ

    Регистрация:
    25 окт 2014
    Сообщения:
    183
    Симпатии:
    72
    А что делает это кусок
    Код:
    CASE WHEN pd.name LIKE "Труба%" THEN 100 ELSE 0 END +
    CASE WHEN pd.name LIKE "%Труба%" THEN 50 ELSE 0 END
    DESC, LOWER(pd.name) ASC LIMIT 10;
    Да и вообще в чем суть запроса?
     
  3. $iD

    $iD Команда форума

    Регистрация:
    13 мар 2012
    Сообщения:
    3.580
    Симпатии:
    1.482
    да возможно. повесить индексы на pd.name и p.model, убрать ведущий %
    убрать LOWER т.к. он не нужен, либо приводить к нижнему регистру на стороне php.

    смысл CASE крайне не ясен и LOWER там так же не нужнен
     
    Master2KAZ нравится это.
  4. Master2KAZ

    Master2KAZ

    Регистрация:
    25 окт 2014
    Сообщения:
    183
    Симпатии:
    72
    Я вот тоже подумал, что выражение
    Код:
    LOWER(pd.name) LIKE "%Труба%"
    не имеет смысла так как pd.name всегда будет строчными буквами, а в %Труба% буква Т прописная.
     
  5. Den1a

    Den1a

    Регистрация:
    16 авг 2013
    Сообщения:
    55
    Симпатии:
    3
    - Запрос для поиска товара.
    - CASE используется для приоритета. В данном примере приоритет имеют товары, где запрос встречается в начале слова, т.е. идет в начале списка.
    --- Добавлено, 5 дек 2017 ---
    - Индексы помогут для LIKE "% %" ?
    - Как найти все результаты, где встречается запрос, если убрать ведущий % ?
    - CASE устанавливает приоритет в моем случае.
    - LOWER можно убрать.

    Спасибо за ответы!
     
  6. Master2KAZ

    Master2KAZ

    Регистрация:
    25 окт 2014
    Сообщения:
    183
    Симпатии:
    72
    А так будет не проще?
    Код:
    ORDER BY pd.name LIKE "Труба%" DESC, pd.name LIKE "%труба%" DESC, LOWER(pd.name) ASC LIMIT 10;
     
  7. ISomeInterface

    ISomeInterface

    Регистрация:
    4 окт 2016
    Сообщения:
    106
    Симпатии:
    76
    CASE используется для релевантной сортировки, то есть
    вхождение слова в начало имени продукта
    "Труба%" THEN 100 ELSE 0 END
    более релевантно чем вхождение в середину
    "%Труба%" THEN 50 ELSE 0 END

    На счет LOWER тут два момента:
    -В данном примере он не нужен так как на стороне сервера поисковая фраза не приводиться к ниж.регистру (поэтому бесполезно применять LOWER)
    -Но для нахождения вхождения фразы все же нужно приводить к общему регистру если к столбцу таблицы применено регистрозависимое сравнение

    По поводу ускорения, индекс будет применен только в случае сравнения по префиксу или полному сравнению. Вхождение тут не катит.
    Можешь сделать сводную таблицу с индексом по словам в которую помести разложенные на слова названия товаров. Это не поможет в поиске вхождения внутри слова но префиксный и полный поиск по словам будет быстрый. Только есть одно но, актуальность состояния таблицы ляжет на твои плечи, нужно будет ее обновлять после импорта или изменения товара. Тут нюансов хватает.

    И еще такой момент, это у тебя еще LIMIT без OFFSET . И чем он будет больше тем время выполнения будет увеличиваться.
     
  8. Master2KAZ

    Master2KAZ

    Регистрация:
    25 окт 2014
    Сообщения:
    183
    Симпатии:
    72
    Так как все-таки лучше? Через case как автор написал, или так
    Код:
    ORDER BY pd.name LIKE "Труба%" DESC, pd.name LIKE "%труба%" DESC, LOWER(pd.name) ASC LIMIT 10;
    Это я для саморазвития интересуюсь, ибо нет таких познаний в БД как у ISomeInterface.
     
  9. ISomeInterface

    ISomeInterface

    Регистрация:
    4 окт 2016
    Сообщения:
    106
    Симпатии:
    76
    Твой подход вообще не сработает, потому что мы считаем общий вес вхождения фразы в конкретные поля.
    К тому же во фразе может быть не одно слово:
    Код:
    CASE WHEN pd.name LIKE "Труба алюминиевая гофрированная%" THEN 500 ELSE 0 END +
    CASE WHEN pd.name LIKE "Труба%" THEN 100 ELSE 0 END +
    CASE WHEN pd.name LIKE "%Труба%" THEN 50 ELSE 0 END+
    CASE WHEN pd.name LIKE "алюминиевая%" THEN 100 ELSE 0 END +
    CASE WHEN pd.name LIKE "%алюминиевая%" THEN 50 ELSE 0 END+
    CASE WHEN pd.name LIKE "гофрированная%" THEN 100 ELSE 0 END +
    CASE WHEN pd.name LIKE "%гофрированная%" THEN 50 ELSE 0 END
    DESC, LOWER(pd.name) ASC LIMIT 10;
    
    И в зависимости от того сколько и где встречаются слова в названии или в других полях формируется релевантность товаров. По которой мы и сортируем результат.
    Это еще без учета морфологии.
     
  10. Master2KAZ

    Master2KAZ

    Регистрация:
    25 окт 2014
    Сообщения:
    183
    Симпатии:
    72
    Може я чего не понял. Если я правильно понял, нужно чтобы сначала шли товары начинающиеся на "Труба..", затем товары в которых есть "труба" в середине названия, а затем остальные товары по алфавиту. Если так, то мой подход работает - я проверял.

    Я сравнил оба варианта на локальном хосте. Оба запроса получили одинаковый результат, только case-вариант сработал за 0,037 с, а мой вариант за 0,032 с. По 3 раза запускал.

    Так какой вариант все-таки лучше? :ScratchOneSHead::Pardon:
     
    Последнее редактирование: 5 дек 2017
  11. ISomeInterface

    ISomeInterface

    Регистрация:
    4 окт 2016
    Сообщения:
    106
    Симпатии:
    76
    Дело не в алфавитной сортировке а в расчете релевантности товара.
    Поисковый запрос: "Труба алюминиевая гофрированная"
    Cортировка по релевантности:
    "Труба алюминиевая гофрированная 40x40" - 800 рейтинг
    "Алюминиевая труба гофрированная 40x40" - 300 рейтинг
    "Труба гофрированная 40x40" -200 рейтинг
    "Псевдоалюминиевая Труба 40x40" 150 рейтинг
    "Псевдоалюминиевая хрень 40x40" 50 рейтинг
     
    Master2KAZ нравится это.
  12. Den1a

    Den1a

    Регистрация:
    16 авг 2013
    Сообщения:
    55
    Симпатии:
    3
    По поводу индексов:

    Индекс используется в конструкции LIKE "запрос%"
    И НЕ используется в конструкции LIKE "%запрос%" или LIKE "%запрос"

    Существует FULLTEXT. Запрос имеет другой вид
    SELECT * FROM articles WHERE MATCH (body) AGAINST ('database');

    Ищет быстро, но только целые слова. % не работает.
    Т.е. для поиска на сайте по частичному совпадению не применить.

    Все верно?
     
  13. ISomeInterface

    ISomeInterface

    Регистрация:
    4 окт 2016
    Сообщения:
    106
    Симпатии:
    76
    Почти, FULLTEXT так же может быть использован для поиска по префиксу слова в режиме IN BOOLEAN MODE, но там свои нюансы.
     
    Последнее редактирование: 6 дек 2017
    Den1a нравится это.
  14. $iD

    $iD Команда форума

    Регистрация:
    13 мар 2012
    Сообщения:
    3.580
    Симпатии:
    1.482
    факап этого подхода в том, что эта хрень сама по себе тормозит или будет тормозить выборку с увеличением кол-ва записей в таблице.

    если очень нужна релевантность, то можно посмотреть в сторону поисковых движков, которые эту возможность предоставляют: sphinx, elasticsearch, solr

    пользовался elasticsearch.
     
    Master2KAZ и Den1a нравится это.
  15. Den1a

    Den1a

    Регистрация:
    16 авг 2013
    Сообщения:
    55
    Симпатии:
    3
    Благодарю за ответы!

    - На данный момент переписал запрос с использованием полнотекстового поиска в режиме IN BOOLEAN MODE. Время выборки значительно сократилось по сравнению с LIKE
    - Установил sphinx. Разбираюсь как с ним работать в данный момент.
     
    $iD нравится это.
Статус темы:
Закрыта.