Today I needed to set up a full text searching system at work. Full text searching is basically searching a set of text fields/columns based on a search term and the results are sorted based on relevance. It is very handy for searching articles and such where there is a lot of text to search through. It is also much faster and more accurate than doing wildcard searches with the LIKE command.
I did a quick search and ended up at this guide which was very good. Give it a read and you should be good to go.
A couple notes:
- When setting up indexes on your text fields, make sure you set them as fulltext and not index
- MySQL ignores any keywords/search terms less than or equal to 3 characters
- When testing make sure you have 3+ rows because if 50% of your rows match a search term, they will be discarded due to MySQL’s 50% threshold rule. ex. trying to test with less than 3 rows in your table you will always get atleast a 50% return rate on rows and nothing will get returned.
Happy Searching =)
[carousel keywords=”mysql” category=”Books” tag=”damnsemi01-20″ showBorder=”True”]