MySQL: Look for the same string in multiple columns

Question

I am trying to make a search-page-like function. I want to make a query to look for "query" in "ColumnA" and "ColumnB" and "ColumnC" and "ColumnD". And select the rows which has the word/phrase "query" in any of those columns. This appears to work:

   SELECT * FROM projects
   WHERE 
   category LIKE '%query%' OR
   name LIKE '%query%' OR 
   description LIKE '%query%'OR 
   keywords LIKE '%query%' OR 
   'type' LIKE '%query%'  
   ORDER BY name ASC

But it is lengthy. Is there any easier or more efficient way of doing this?

best option is to create an many to many relationship with search words llnkt to your projects table this way you can avoid using like '%%' (would require an full index scan or full table scan) or the use off an FULL TEXT index.. — Raymond Nijland
– Raymond Nijland, Commented Nov 30, 2013 at 14:40

Community · Accepted Answer · 2017-05-23 11:54:19Z

23

Simple workaround:

SELECT * 
FROM projects 
WHERE 
    CONCAT(category,name,description,keywords,type) LIKE '%query%' 
ORDER BY name ASC;

You can add separators between columns if needed:

SELECT * 
FROM projects 
WHERE 
    CONCAT(category,"|",name,"|",description,"|",keywords,"|",type) LIKE '%query%' 
ORDER BY name ASC;

You can also use a fulltext search (you need to create a fulltext index as described here: How do FULLTEXT INDEXES on multiple columns work?)

SELECT *, MATCH (category,name,description,keywords,type) AGAINST ('query') AS score FROM projects WHERE MATCH (category,name,description,keywords,type) AGAINST ('query');

edited May 23, 2017 at 11:54

CommunityBot

11 silver badge

answered Nov 30, 2013 at 14:27

Xtophe

2,3171 gold badge21 silver badges41 bronze badges

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

1 Comment

Paul Spiegel Over a year ago

You can use CONCAT_WS('|', category, name, ...) for your second query. I would even use it for the first query with an empty separator, because CONCAT_WS() will ignore NULL columns, while CONCAT() will return NULL if only one column is NULL.

Jason Heo · Accepted Answer · 2013-11-30 15:02:09Z

But it is lengthy.

I think this is not problem. query can be generated client side.

Is there any easier or more efficient way of doing this?

use Sphinx or Solr with MySQL. It is not difficult and super fast. Here is Sphinx example. http://www.ibm.com/developerworks/library/os-php-sphinxsearch/

and I think CONCAT is not efficient than yours, there is cost to concatenate columns (some columns can be long text). CONCAT(name, description) LIKE '%query%' reads name and description and concat two values after that LIKE is applied. that means all columns READ twice, while your query can be completed just first column is matched. All condition is "OR", so category column matches %query% that row does not need to be compared to 'name' column.

FYI

just FYI, below query can check, which column has more 'query'

SELECT name,
  (LENGTH(category) - LENGTH(REPLACE(category, 'query', ''))) / LENGTH('query') as category_match_cnt,
  (LENGTH(name) - LENGTH(REPLACE(name, 'query', ''))) / LENGTH('query') as name_match_cnt,
  (LENGTH(description) - LENGTH(REPLACE(description, 'query', ''))) / LENGTH('query') as desc_match_cnt,

Collectives™ on Stack Overflow

MySQL: Look for the same string in multiple columns

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related