CODEWITHSUNDEEP
mysqlsearchfull-text-search
thelastray
thelastray

Reputation: 472

Mysql LIKE or FULLTEXT search - Which one to be used here?

I am working on a php-mysql based website, designed primarily for searching. I was earlier using LIKE operator in all my queries. But my sql is becoming too large and complicated, as there could be multiple keywords to be searched through the list of fields. I found FULLTEXT search not only improves the performance, but also the query becomes more readable. But for FULLTEXT search I have the following problems:

  1. I may have to use wild characters, atleast at the end, eg.

$sql = mysql_query("SELECT * FROM table WHERE MATCH (field1,field2, field3,field4) AGAINST ('+val1* + val2* + val3*');");

instead of

$sql = mysql_query("SELECT * FROM table WHERE (filed1 LIKE '%val1%' OR filed2 LIKE '%val1%' OR filed3 LIKE '%val1%' OR filed4 LIKE '%val1%') AND (filed1 LIKE '% val2%' OR filed2 LIKE '% val2%' OR filed3 LIKE '% val2%' OR filed4 LIKE '% val2%') AND (filed1 LIKE '% val3%' OR filed2 LIKE '% val3%' OR filed3 LIKE '% val3%' OR filed4 LIKE '% val3%')");

Will the previous query really improve my performance against the second one?

2. For advance search the user inputs could be either keywords that match the result or keywords that do not match the result. So my sql goes like the following:

$sql = mysql_query("SELECT * FROM table WHERE (filed1 LIKE '%val1%' OR filed2 LIKE '%val1%' OR filed3 LIKE '%val1%' OR filed4 LIKE '%val1%') AND (filed1 LIKE '% val2%' OR filed2 LIKE '% val2%' OR filed3 LIKE '% val2%' OR filed4 LIKE '% val2%') AND (filed1 LIKE '% val3%' OR filed2 LIKE '% val3%' OR filed3 LIKE '% val3%' OR filed4 LIKE '% val3%')") AND (filed1 NOT LIKE '%val1%' OR filed2 NOT LIKE '%val1%' OR filed3 NOT LIKE '%val1%' OR filed4 NOT LIKE '%val1%') AND (filed1 NOT LIKE '% val2%' OR filed2 NOT LIKE '% val2%' OR filed3 NOT LIKE '% val2%' OR filed4 NOT LIKE '% val2%') AND (filed1 NOT LIKE '% val3%' OR filed2 NOT LIKE '% val3%' OR filed3 NOT LIKE '% val3%' OR filed4 NOT LIKE '% val3%')");

This is not quiet readable, and I feel will slow down the search performance. But I am not sure whether this can be accomplished using FULLTEXT search. If yes then what will be the query?

And finally, my question is should I stick with WHERE/LIKE for search or go for FULLTEXT search?

P.S. My database is a small one with merely 1000 rows and 10 fields.

Upvotes: 1

Views: 650

Answers (1)

Kris
Kris

Reputation: 14458

For advanced text based searching, the answer is that neither is optimal. Instead, you should use a dedicated text retrieval search engine like Lucene and SolR.

They are built around this problem. Better to use the correct tool for the job.

Upvotes: 0

Related Questions