How to implement a Keyword Search in MySQL?

Question

HI all, I am really a new sq l programmer, can some one help me in solving this. 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 be reside in any of the field of the said table.

Answer 1

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.

Answer 2

For a single keyword on VARCHAR fields you can use LIKE:

SELECT id, category, location
FROM table
WHERE
(
    category LIKE '%keyword%'
    OR category 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')

Answer 3

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

Answer 4

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