How to speed up LIKE in mysql?

Question

SQL

How to speed up LIKE in mysql on text field? in the table of 100 records?

Answer 1

The most correct answer is not to use like at all.
And it's better to work with fulltext outside of mysql
The answer for the poor is to use only like with a prefix i.e. kind somestring%
None%somestring%

Answer 2

if the query is for a strict match or type "query%", then a regular b-tree index will help (if you have varchar and the field length allows, of course). If "%query%" or "%query" then there is no way to speed it up. You can then set up a full-text index and use full-text search instead of LIKE.

Answer 3

First, try to abandon the subquery.

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

Answer 4

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

explain?

Answer 6

Try sphinx - it's simple and convenient)