How to implement a Keyword Search in MySQL?

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

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.

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