SQL full text search vs "LIKE"

sql database full-text-search
Kevin Pang picture Kevin Pang · Jan 25, 2009 · Viewed 21.2k times · Source

Let's say I have a fairly simple app that lets users store information on DVDs they own (title, actors, year, description, etc.) and I want to allow users to search their collection by any of these fields (e.g. "Keanu Reeves" or "The Matrix" would be valid search queries).

What's the advantage of going with SQL full text search vs simply splitting the query up by spaces and doing a few "LIKE" clauses in the SQL statement? Does it simply perform better or will it actually return results that are more accurate?

Answer

AnthonyWJones picture AnthonyWJones · Jan 26, 2009

Full text search is likely to be quicker since it will benefit from an index of words that it will use to look up the records, whereas using LIKE is going to need to full table scan.

In some cases LIKE will more accurate since LIKE "%The%" AND LIKE "%Matrix" will pick out "The Matrix" but not "Matrix Reloaded" whereas full text search will ignore "The" and return both. That said both would likely have been a better result.

Related questions

Performance of like '%Query%' vs full text search CONTAINS query

I have a situation where I would like to search a single word. For that scenario, which query would be good from a performance point of view? Select Col1, Col2 from Table Where Col1 Like '%Search%' or Select …

Read More
mysql query - contains?

how can i search for a particular string in mysql? I tried using contains- gives me error: SELECT r.name , r.network , r.namestring , i.name , r.rid , i.id , d.dtime , d.ifInOctets , d.description FROM router AS r …

Read More
Order SQL by strongest LIKE?

I have the following query: SELECT * FROM table_name WHERE (col_1 LIKE '%$keyword%' OR col_2 LIKE '%$keyword%' OR col_3 LIKE '%$keyword%') AND .... <some optional filters> ... is there a strategy to sort based …

Read More