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

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%'

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.

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.

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%'

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 ...

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

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% ...