Как ускорить работу LIKE в mysql?

Question

[Vanya Huk]

Как ускорить работу LIKE в mysql по текстовому полю? в таблице 100к записей?

Comments

[Rsa97]

Пример запроса приведите.

[Vanya Huk]

Rsa97:

select `id`, `title`, `slug`, `label`, `price`, `old_price`, `category_id`, `picture_id`, `brand_id` from `products` where (exists (select * from `brands` where `products`.`brand_id` = `brands`.`id` and (`title` like '%лампа%' or `tags` like '%лампа%')) or `title` like '%лампа%') group by `products`.`id` order by `products`.`sales` desc, `products`.`views` desc limit 18

Answer 1

[Fortop]

Ответ самый правильный - не использовать like вовсе.
А работать с fulltext и лучше вне mysql

Ответ для бедных - использовать только like с префиксом т.е. вида somestring%
Никаких %somestring%

Answer 2

[Vit]

если запрос на строгое совпадение или типа "query%", то обычный b-tree индекс поможет (если у вас varchar и длина поля позволяет, конечно). Если "%query%" или "%query" то особо никак не ускорить. Можно тогда настроить полнотекстовый индекс и использовать полнотекстовый поиск вместо LIKE.

Comments

[Vanya Huk]

Vit : а можете навести пример полнотекстового поиска для запроса

select `id`, `title`, `slug`, `label`, `price`, `old_price`, `category_id`, `picture_id`, `brand_id` from `products` where (exists (select * from `brands` where `products`.`brand_id` = `brands`.`id` and (`title` like '%лампа%' or `tags` like '%лампа%')) or `title` like '%лампа%') group by `products`.`id` order by `products`.`sales` desc, `products`.`views` desc limit 18

Answer 3

[Rsa97]

Для начала попробуйте отказаться от вложенного запроса.

SELECT `p`.`id`, `p`.`title`, `p`.`slug`, `p`.`label`, `p`.`price`, `p`.`old_price`, `p`.`category_id`, `p`.`picture_id`, `p`.`brand_id` 
  FROM `products` AS `p`
  LEFT JOIN `brands` AS `b` ON `b`.`id` = `p`.`brand_id`
  WHERE `p`.`title` LIKE '%лампа%' OR `b`.`title` LIKE '%лампа%' OR `b`.`tags` like '%лампа%'
  GROUP BY `products`.`id` order by `products`.`sales` desc, `products`.`views` desc 
  LIMIT 18

Comments

[Adamos]

JOIN, собственно, тоже ни к чему - поля из второй таблицы никуда не идут.

[Rsa97]

Adamos: По этим полям выполняется поиск во WHERE

[Adamos]

Rsa97: Это понятно, просто я исхожу из мысли, что брендов сильно меньше, чем товаров. Поэтому вложенный IN должен тратить меньше времени, чем WHERE по тем же полям в каждой строчке перемножения.

[Rsa97]

Adamos: Тоже может быть. Только надо смотреть, будет ли вложенный запрос выполняться один раз (как в вашем варианте) или для каждой строки (как у автора).

[Vanya Huk]

Rsa97: а можете навести пример полнотекстового поиска для запроса

[Rsa97]

Vanya Huk: Для полнотекстового поиска надо сначала таблицу модифицировать - перевести таблицу на движок MyISAM и добавить полнотекстовые индексы по нужным полям. При этом на этой таблице перестанут работать транзакции, внешние ключи и построчная блокировка.

[shagguboy]

еревести таблицу на движок MyISAM и добавить полнотекстовые индексы по нужным полям.

Чего?

[Vanya Huk]

shagguboy: нельзя так перевести, innobd - бозопаснее

[Rsa97]

Vanya Huk: Сорри, начиная с версии MySQL 5.6 полнотекстовый поиск поддерживается и в InnoDB.
Значит просто добавляете FULLTEXT индекс на нужные колонки

CREATE FULLTEXT INDEX `productsFT` ON `products`(`title`);
CREATE FULLTEXT INDEX `brandsFT` ON `brands`(`title`, `tags`);

и используете вместо LIKE

WHERE MATCH(`p`.`title`) AGAINST ('лампа') OR MATCH(`b`.`title`, `b`.`tags`) AGAINST ('лампа')

[Vanya Huk]

Rsa97: возвращает ноль строк

[Rsa97]

Vanya Huk: И ошибок никаких не выдаёт?

[Vanya Huk]

Rsa97: нет

Answer 4

[Adamos]

select `id`, `title`, `slug`, `label`, `price`, `old_price`, `category_id`, `picture_id`, `brand_id` 
from `products` where 
`brand_id` IN (select id from `brands` where `title` like '%лампа%' or `tags` like '%лампа%') 
OR `title` like '%лампа%'
group by `products`.`id`
order by `products`.`sales` desc, `products`.`views` desc 
limit 18

Answer 5

[shagguboy]

Explain?

Comments

[Adamos]

Тут без EXPLAIN понятно, что у ТС в запросе на каждую строчку products делается запрос к brands с двумя LIKE %...%.
Причем то и дело разный (привет кэшу). Как тут не тормозить?

Answer 6

[redya69]

Попробуйте sphinx - он простой и удобный)