How to make a good sql search with like

Question

For years when I want my user to search some field in my database where he can type anything he wants I use an algorithm to break the words and search each word separetely... a mess.

For example, if the user types in the search box "aaa bbb ccc" I dont like using:

SELECT id 
  FROM table 
 WHERE description LIKE '%aaa bbb ccc%'

Cause sometimes the user types things out of order and the query above wouldng find. What I usually do is breaking the string and concatenating it with PHP so the result becomes:

SELECT id 
  FROM table 
 WHERE description LIKE '%aaa%' 
   AND description LIKE '%bbb%' 
   AND description LIKE '%ccc%'

But today after talk to a friend I was wondering if there is some native way to do this faster using MY SQL?

Yes, this is called [full text search](https://dev.mysql.com/doc/refman/5.7/en/fulltext-search.html). — Sergio Tulentsev, Sep 24 '17 at 19:49
@SergioTulentsev "When MATCH() is used in a WHERE clause, as in the example shown earlier, the rows returned are automatically sorted with the highest relevance first." THAT'S PRETTY GOOD!!!!! If you post an answer I will accept it! I WAS NEVER aware of that, it's pretty magical! — Samul, Sep 24 '17 at 19:51
SELECT * FROM table WHERE MATCH (description) AGAINST ('aaa bbb ccc' IN NATURAL LANGUAGE MODE); Try this and tell me the result. — Shifat, Sep 24 '17 at 20:43

score 2 · Accepted Answer · answered Sep 24 '17 at 19:53

2

What you want to do is called full text search and most relational databases support it nowadays, including mysql.

answered Sep 24 '17 at 19:53

Sergio Tulentsev

226,338
43
373
367

Do you know any good reference? Mysql page is so confusing and other pages I found provide contraditory examples... Most of them say the full text search always return results even if only one word of the many words the user typed. – Samul Sep 24 '17 at 20:00
1

mysql documentation is _the_ reference. :) – Sergio Tulentsev Sep 24 '17 at 20:02
I read a lot but I still cant find an answer to my question... cause using your suggested answer sometimes it returns resulsts with NOT all the words. For example, user searching for "aaa bbb ccc" still returns resuls containing only "aaa bbb"! I am using this -> SELECT * FROM `itens_cadastrados` WHERE MATCH(titulo) AGAINST("aaa bbb ccc") – Samul Sep 24 '17 at 20:05
@Samul: I am sure it's just a matter of applying a proper query modifier. MATCH ALL instead of MATCH (or something. I just made this modifier up, haven't used mysql's full text search myself) – Sergio Tulentsev Sep 24 '17 at 20:07
@Samul [The documentation](https://dev.mysql.com/doc/refman/5.7/en/fulltext-search.html) is pretty clear. There's several examples, plus comments at the end that usually add evne more context. – tadman Sep 24 '17 at 22:16

score 0 · Answer 2 · answered Sep 24 '17 at 20:02

0

I think you can use REGEXP. For example:

Select * from table where description REGEXP 'aaa|bbb|ccc'

answered Sep 24 '17 at 20:02

huseyinm88

13
1
3

good ;) But I dont think this is optimized to work in a huge database as the one I am using (200k + rows with 18 columns) – Samul Sep 24 '17 at 20:04

score 0 · Answer 3 · answered Sep 24 '17 at 20:36

0

FULLTEXT Searches are really fast. INSTR or locate works better than REGEXP. But it depends on various factors. More comparison here

SELECT * from table where INSTR(description, 'aaa') >0 
SELECT * from table where LOCATE(description, 'aaa') >0

answered Sep 24 '17 at 20:36

Valli

1,440
1
8
13

How to make a good sql search with like

3 Answers3