How to implement a Keyword Search in MySQL

Question

I am new to SQL programming   I have a table job where the fields are id  position  category  location  salary range  description  refno   I want to implement a keyword search from the front end  The keyword can reside in any of the fields of the above table   This is the query I have tried but it consist of so many duplicate rows   SELECT     a        b catname FROM     job a      category b WHERE     a catid   b catid AND     a jobsalrange    15001-20000  AND     a jobloc    Berkshire  AND     a jobpos LIKE   sales   OR     a jobloc LIKE   sales   OR     a jobsal LIKE   sales   OR     a jobref LIKE   sales   OR     a jobemail LIKE   sales   OR     a jobsalrange LIKE   sales   OR     b catname LIKE   sales

User · Answer

I will explain the method i usally prefer:

First of all you need to take into consideration that for this method you will sacrifice memory with the aim of gaining computation speed. Second you need to have a the right to edit the table structure.

1) Add a field (i usually call it "digest") where you store all the data from the table.

The field will look like:

"n-n1-n2-n3-n4-n5-n6-n7-n8-n9" etc.. where n is a single word

I achieve this using a regular expression thar replaces " " with "-". This field is the result of all the table data "digested" in one sigle string.

2) Use the LIKE statement %keyword% on the digest field:

SELECT * FROM table WHERE digest LIKE %keyword%

you can even build a qUery with a little loop so you can search for multiple keywords at the same time looking like:

SELECT * FROM table WHERE 
 digest LIKE %keyword1% AND 
 digest LIKE %keyword2% AND 
 digest LIKE %keyword3% ...

User · Answer

Ideally  have a keyword table containing the fields   Keyword Id Count  possibly    with an index on Keyword  Create an insert update delete trigger on the other table so that  when a row is changed  every keyword is extracted and put into  or replaced in  this table   You ll also need a table of words to not count as keywords  if  and  so  but         In this way  you ll get the best speed for queries wanting to look for the keywords and you can implement  relatively easily  more complex queries such as  contains Java and RCA1802     LIKE  queries will work but they won t scale as well

User · Answer

Personally  I wouldn t use the LIKE string comparison on the ID field or any other numeric field  It doesn t make sense for a search for ID   216  to return 16216  21651  3216087  5321668     and so on and so forth  likewise with salary   Also  if you want to use prepared statements to prevent SQL injections  you would use a query string like   SELECT   FROM job WHERE  position  LIKE CONCAT              OR

User · Answer

You can find another simpler option in a thread here  Match Against   with a more detail help in 11 9 2  Boolean Full-Text Searches  This is just in case someone need a more compact option  This will require to create an Index FULLTEXT in the table  which can be accomplish easily   Information on how to create Indexes  MySQL   MySQL FULLTEXT Indexing and Searching  In the FULLTEXT Index you can have more than one column listed  the result would be an SQL Statement with an index named search   SELECT   MATCH   column   AGAINST   keyword1   keyword2   keyword3    as relevance  FROM  documents USE INDEX search  WHERE MATCH   column   AGAINST   keyword1   keyword2   keyword3   IN BOOLEAN MODE  ORDER BY relevance    I tried with multiple columns  with no luck  Even though multiple columns are allowed in indexes  you still need an index for each column to use with Match Against Statement   Depending in your criterias you can use either options

User · Answer

I know this is a bit late but what I did to our application is this  Hope this will help someone tho  But it works for me    SELECT   FROM  landmarks  WHERE  landmark name  OR  landmark description  OR  landmark address  LIKE   keyword  OR  landmark name  OR  landmark description  OR  landmark address  LIKE  keyword    OR  landmark name  OR  landmark description  OR  landmark address  LIKE   keyword

User · Answer

SELECT        FROM      yourtable WHERE      id LIKE   keyword        OR position LIKE   keyword       OR category LIKE   keyword       OR location LIKE   keyword       OR description LIKE   keyword       OR refno LIKE   keyword

User · Answer

For a single keyword on VARCHAR fields you can use LIKE   SELECT id  category  location FROM table WHERE       category LIKE   keyword       OR location LIKE   keyword       For a description you re usually better adding a full text index and doing a Full-Text Search  MyISAM only    SELECT id  description FROM table WHERE MATCH  description  AGAINST  keyword1 keyword2

[sql] How to implement a Keyword Search in MySQL?

Examples related to sql

Examples related to mysql

Examples related to search