Today is a good day to code

Fulltext Searching in MySQL

Posted: December 31st, 1969 | Author: | Filed under: Uncategorized | No Comments »

Fulltext Searching in MySQL

Picture of Irv Owens Web DeveloperMySQL has been around for a while, and it still remains my favorite database development platform because even though I think I know it, new features are added that, though sometimes challenging, enable it to compete directly with Microsoft SQL Server. Right now at work, we are all using Microsoft SQL Server, though it seems that everytime I find a new feature in SQL Server, it is also available in MySQL. The feature that I have found that I love is the combinatinon of fulltext indexing and searching.

The documentation is pretty extensive in this area, so I won't copy too much of it here, but the gist of it is that fulltext searching will enable you to create an index via the

FULLTEXT (field1, field2)

command. It is important to remember to index the fields that you want to search at the same time or it won't work. I'll quote the documentation for the SQL.

SELECT id, field1, MATCH (field1,field2) AGAINST
('text to be searched') AS score
FROM table WHERE MATCH (table1,table2) AGAINST
('text to be searched')

This will generate a search for the 'text to be searched' in the fields field1,field2. The results will come back with the text in field1, and the results as score.

Fulltext searching in MySQL is reasonably quick, at least as fast as doing everything on the application server side. It has the added benefit of leaving your app server free to serve. The SQL is quirky in that you are casting your results in as score. The MATCH keyword will always return the results, so if you put it after your select keyword without adding any other fields, you will just get a column of numbers that indicate the relative strength of the matches.

The SQL search has a few more quirks, namely that there is a 50% rule that governs the relevance of the search string. For example, if you have a blog about Microsoft, and you do a search for Microsoft, you probably will get no results returned. This is because probably every article will have the word Microsoft included somewhere. Also, a space between words acts as an OR instead of an AND. This may confuse users. I have had some trouble getting the boolean searching to work with my host's MySQL 3.23 installation, but my 4.1 version seems to work fine. The search excludes any words under four letters in length. This is to limit the size of the index, as well as to get rid of words that won't be relevant anyway such as 'get.'

All in all, it is definately a useful tool, and can save a ton of work. If you have control of the DB server, then you can refine the search defaults to get the most accurate results for your data. You can check it out at Search Owens Performance Blog.

MySQL documentation for fultext searching