MySQL: Look for the same string in multiple columns

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?

Contents hide

Answers:

Method 1

Method 2

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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');

Method 2

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,

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating