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

Здравствуйте.
Возможно ли оптимизировать данный запрос?
# 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;

 

А что делает это кусок

Код:

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;

Да и вообще в чем суть запроса?

 

Я вот тоже подумал, что выражение

Код:

LOWER(pd.name) LIKE "%Труба%"

не имеет смысла так как pd.name всегда будет строчными буквами, а в %Труба% буква Т прописная.

 

- Запрос для поиска товара.
- CASE используется для приоритета. В данном примере приоритет имеют товары, где запрос встречается в начале слова, т.е. идет в начале списка.

--- Добавлено, 5 дек 2017 ---

- Индексы помогут для LIKE "% %" ?
- Как найти все результаты, где встречается запрос, если убрать ведущий % ?
- CASE устанавливает приоритет в моем случае.
- LOWER можно убрать.

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

 

А так будет не проще?

Код:

ORDER BY pd.name LIKE "Труба%" DESC, pd.name LIKE "%труба%" DESC, LOWER(pd.name) ASC LIMIT 10;

 

CASE используется для релевантной сортировки, то есть
вхождение слова в начало имени продукта
"Труба%" THEN 100 ELSE 0 END
более релевантно чем вхождение в середину
"%Труба%" THEN 50 ELSE 0 END

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

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

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

 

Так как все-таки лучше? Через case как автор написал, или так

Код:

ORDER BY pd.name LIKE "Труба%" DESC, pd.name LIKE "%труба%" DESC, LOWER(pd.name) ASC LIMIT 10;

Это я для саморазвития интересуюсь, ибо нет таких познаний в БД как у ISomeInterface.

 

Твой подход вообще не сработает, потому что мы считаем общий вес вхождения фразы в конкретные поля.
К тому же во фразе может быть не одно слово:

Код:

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;

И в зависимости от того сколько и где встречаются слова в названии или в других полях формируется релевантность товаров. По которой мы и сортируем результат.
Это еще без учета морфологии.

 

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

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

Так какой вариант все-таки лучше?

 

Дело не в алфавитной сортировке а в расчете релевантности товара.
Поисковый запрос: "Труба алюминиевая гофрированная"
Cортировка по релевантности:
"Труба алюминиевая гофрированная 40x40" - 800 рейтинг
"Алюминиевая труба гофрированная 40x40" - 300 рейтинг
"Труба гофрированная 40x40" -200 рейтинг
"Псевдоалюминиевая Труба 40x40" 150 рейтинг
"Псевдоалюминиевая хрень 40x40" 50 рейтинг

 

По поводу индексов:

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

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

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

Все верно?

 

Почти, FULLTEXT так же может быть использован для поиска по префиксу слова в режиме IN BOOLEAN MODE, но там свои нюансы.

 

Благодарю за ответы!

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