I am trying to determine the best way to use match against in my insite search engine. I want the most efficient and fastest way to do it. I have two choices of sql statements:
SELECT *, MATCH(keywords) AGAINST
('keyword keyword2 keyword3' IN BOOLEAN MODE) AS relevance
FROM `table1`
WHERE MATCH(keywords) AGAINST ('keyword keyword2 keyword3'
IN BOOLEAN MODE)
ORDER BY relevance DESC;
the keywords columns contains keywords that describe the record, that I manually do (manual indexing). The columns is indexed as FULLTEXT INDEX.
Other sql is:
SELECT *, MATCH(column1,column2,column3) AGAINST
('keyword keyword2 keyword3' IN BOOLEAN MODE) AS relevance
FROM `table1`
WHERE MATCH(column1,column2,column3) AGAINST ('keyword keyword2 keyword3'
IN BOOLEAN MODE)
ORDER BY relevance DESC;
Where the three columns are the ones that include the data for matching.
I am still new to the concept of match against. So, I am still experimenting around. I would like to know which one should I use. Also, I have another question regarding phrases. I added a phrase in the keywords between "". Though, when I tried to search for it and even though I added + before it in the sql statement. I could match the phrase with only one word from it. Thanks for your time and appreciate any comments. I am using mysql.