Keyword search using PHP MySql?

Question

I have title (varchar), description (text), keywords (varchar) fields in my mysql table.

I kept keywords field as I thought I would be searching in this field only. But I now require to search among all three fields. so for keywords "word1 word2 word3", my query becomes

SELECT * FROM myTable
WHERE (
name LIKE '%word1%' OR description LIKE '%word1%' OR keywords LIKE '%word1%' 
OR name LIKE '%word2%' OR description LIKE '%word2%' OR keywords LIKE '%word2%' 
OR name LIKE '%word3%' OR description LIKE '%word3%' OR keywords LIKE '%word3%') 
AND status = 'live'

Looks a bit messy but this works. But now I need to implement synonym search. so for a given word assuming there are a few synonyms available this query becomes more messy as I loop through all of the words. As the requirements are getting clearer, I will need to join this myTable to some other tables as well.

So

• Do you think the above way is messy and will cause problems as the data grow?

• How can I avoid above mess? Is there any cleaner solution I can go by? Any example will help me.

• Is there any other method/technique you can recommend to me?

With thanks

EDIT

@Peter Stuifzand suggested me that I could create one search_index table and store all 3 fields (title,keyword,desc) info on that and do full text search. I understand that additionally this table will include reference to myTable primary key as well.

But my advanced search may include joining mytable with Category table, geographic_location table (for searching within 10, 20 miles etc), filtering by someother criteria and of course, sorting of search results. Do you think using mysql fulltext will not slow it down?

Solution 1

When your queries are getting out of hand, it's sometimes better to write parts of it in SQL and other parts in your programming language of choice.

And you could also use fulltext search for searching. You can create separate table with all fields that you want to search and add the FULLTEXT modifier.

CREATE TABLE `search_index` (
    `id` INT NOT NULL,
    `data` TEXT FULLTEXT,
);

SELECT `id` FROM `search_index` WHERE MATCH(`data`) AGAINST('word1 word2 word3');

Solution 2

One more way (sometimes it's better but it depends...)

SELECT 
    id, name, description, keywords 
FROM 
    myTable
WHERE
    name REGEXP '.*(word1|word2|word3).*' OR
    description REGEXP '.*(word1|word2|word3).*' OR
    keywords REGEXP '.*(word1|word2|word3).*'
;

PS: But MATCH(cols) AGAINST('expr') possibly is better for your case.