Full Text Searching in MySQL

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”]

Continue Reading

  • How to over come this?

    • Skye

      Over come what?

  • Mark

    Thanks for posting this link, very useful.

  • Martin

    This post help me sooo much!