Tutorial: MySQL FullText searching
Full-text searching is performed using MATCH() ... AGAINST syntax. MATCH() takes a comma-separated list that names the columns to be searched. AGAINST takes a string to search for, and an optional modifier that indicates what type of search to perform. The search string must be a literal string, not a variable or a column name.
So instead of using: Where RawrTable is the table and RawrField is a field you want to search upon, you can use FullText in the following syntax: That will perform a slightly more logical search on the table RawrTable. Right, things can get a bit more complicated so with that basic premise down, I'll explain a bit more.
To set up FullText search you need to create a FullText index on your table
You need to set up the index on the table before MySQL will recognise the MATCH/AGAINST syntax. This is done using the following code: That creates a FullText index on the RawrField field. If you wish to add multiple fields to the FullText index (as Seopher.com does) then just add a comma after "RawrField" and add the second field, eg "ADD FULLTEXT(RawrField, RawrField2)".
The results are still quite basic, so let's improve them slightly
You're still doing quite a basic comparison at the moment and the results won't be ordered in a logical way yet. So let's improve the search feature a little more by scoring the results. The syntax for the above statement should be right but I'm doing this off the top of my head. What you've got there is a basic select statement followed by the creation of a "score". The score assigns a relavence score to each of the records matched. Therefore when you've made the relevancy and completed the WHERE statement, you can simply order by score in descending order to see the most relevant results at the top.
So there you have it, a really straight forward tutorial explaining the basics of MySQL FullText searching. There are many things you can do to improve the quality of the results returned (I was contemplating writing my own search algorithm to do this but MySQL does it okay for now). It may be worth looking at FullText Boolean searches because they can offer more accurate results in the right circumstances. I hope this was of some use.
Enjoy this article? Why not subscribe to the full RSS feed?
