CODEWITHSUNDEEP
mysqlsearchfull-text-searchwhere-clause
Scott MacDonald
Scott MacDonald

Reputation: 23

MySQL Multi Field Search Using Like Clause

I'm trying to search multiple fields using the Like clause in MySQL and I can't seem to get back any results. Here is the query I am using:

SELECT player_id, 1st_name, last_name, birth_date, nationality, pasition 
FROM `people` 
WHERE player_id  LIKE '%Keyword%' 
  OR 1st_name    LIKE '%Keyword%' 
  OR last_name   LIKE '%Keyword%' 
  OR birth_date  LIKE '%Keyword%' 
  OR nationality LIKE '%Keyword%'
  OR pasition    LIKE '%Keyword%' 
ORDER BY `people`.`1st_name` ASC LIMIT 0 , 60

Keyword is a variable passed by php.

Upvotes: 0

Views: 656

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562230

You should use a FULLTEXT index for this. You can search multiple columns, and it will run hundreds or thousands of times faster than using LIKE with wildcards.

CREATE FULLTEXT INDEX ft_people ON `people` 
  (player_id, `1st_name`, last_name, birth_date, nationality, position);

SELECT player_id, 1st_name, last_name, birth_date, nationality, pasition 
FROM `people` 
WHERE MATCH(player_id, `1st_name`, last_name, birth_date, nationality, position) 
  AGAINST('+Keyword' IN BOOLEAN MODE) 
ORDER BY `people`.`1st_name` ASC LIMIT 0 , 60

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

Note that in MySQL prior to 5.6, FULLTEXT indexes are only supported in the MyISAM storage engine.

Upvotes: 1

Related Questions