Speed up rails like query

Question

How can I speed up string matching query which uses SQL like? The code looks something like

CurrentSchool.all.includes(:school).where(is_middle_school: true).where("name like '%#{search}%'")

This is search box suggestion query that has to fast for good UX.

I am using MySQL DB. Would adding an index on column improve like performance? — 2ank3th, Aug 24 '17 at 20:40
Try using `CurrentSchool.all.includes(:school).where(is_middle_school: true).where("name like '%#{search}%'").explain`. It will display how many rows are examined. After adding an index, you can run it again and see whether the number of rows has been reduced — Igor Drozdov, Aug 24 '17 at 20:46
You should write `.where("name like '%#{search}%'")` as `.where("name like ?", "'%#{search}%'")`. — mu is too short, Aug 24 '17 at 20:48
https://stackoverflow.com/questions/2481528/mysql-like-performance-boost suggests that index is only useful if query is '#{search}%' — 2ank3th, Aug 24 '17 at 20:49

score 0 · Answer 1 · answered Aug 24 '17 at 20:48

0

You should use a fulltext index to get a performance boost using a '%something%' like query.

answered Aug 24 '17 at 20:48

Fede Bonisconti

1 Answers1