Searching For Multiple Values - SQL

Question

Is this the most efficient way to search multiple values at once?

SELECT *
  FROM `table`
 WHERE (    (title LIKE %search term 1%)
         OR (description LIKE %search term 1%)
         OR (title LIKE %search term 2%)
         OR (description LIKE %search term 2%)
         OR (title LIKE %search term 3%)
         OR (description LIKE %search term 3%)
         OR (title LIKE %search term 4%)
         OR (description LIKE %search term 4%)
         OR (title LIKE %search term 5%)
         OR (description LIKE %search term 5%)
       )
;

%search% means full table scan so it doesn't matter then how much more ORs you have — Cjxcz Odjcayrwl
– Cjxcz Odjcayrwl, Commented Jan 24, 2012 at 19:46

ruakh · Accepted Answer · 2012-01-24 19:42:11Z

1

If you really need to find all records where either title or description contains any of those search terms, then that query is about as good as you can do without either (1) restructuring your schema, e.g. adding a table_search_terms table with columns table_id and search_term (so that you can write SELECT table.* FROM table JOIN table_search_terms on table.id = table_search_terms.table_id WHERE search_term = 'search term 1' OR ...), or (2) installing special tools for full-text search indexing (such as Lucene). Any smaller change, like using INSTR or REGEXP rather than LIKE, might speed the query up slightly (or might slow it down slightly), but will not have a very noticeable impact.

answered Jan 24, 2012 at 19:42

ruakh

185k29 gold badges292 silver badges324 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Andrew · Accepted Answer · 2012-01-24 21:11:31Z

1

It looks like you might want to look into fulltext searching:

http://dev.mysql.com/doc/refman/5.1/en/fulltext-search.html

Your query would look like:

SELECT * FROM table 
WHERE MATCH (title,description)
AGAINST ('"search term 1" "search term 2" "search term 3" "search term 4" "search term 5"' IN BOOLEAN MODE);

You'll want to define a fulltext index on your table, but it will be way faster than your original query in almost every case.

edited Jan 24, 2012 at 21:11

answered Jan 24, 2012 at 20:00

Andrew

4,62429 silver badges31 bronze badges

Collectives™ on Stack Overflow

Searching For Multiple Values - SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related