Hello World

Creating a fulltext tag search

A boolean fulltext search basically has everything we want: It supports AND, OR, NOT, expression grouping etc.

The syntax for doing it is also much much cleaner and the table structure can also be simplified.

Instead of having two tables for just keeping the tag data, we can just have a single table for the tags and the tagged data. With this approach, we can store the tags in a single varchar or text column. This column just needs a fulltext index, which can be created with...

ALTER TABLE myTable ADD FULLTEXT tags

Provided our table has a column called tags, the above query would create a fulltext index for it. You can also define the fulltext index for a column when creating the table by adding FULLTEXT(columname) to the column definition part.

Now, if we had some data in the table, we could do:

SELECT data FROM myTable WHERE MATCH (tags) AGAINST ('hello world' IN BOOLEAN MODE)

Which would match rows with hello or world in the tags column.

As you can see, it's obviously much cleaner and simpler than the other approach.
You can also use searches like +hello -world foo* which would match rows which have tag "hello", not "world" and any tag starting with "foo", such as just "foo" or "foobar"

As you can see, this saves us from writing a custom search string parser which is required in the other approach to generate the proper SQL query.

Drawbacks

Using a fulltext index comes with some drawbacks, though.

• You can't create tag clouds or such very easily. Because all your tags are in the columns with the data, you can't quickly calculate how many times a certain tag is used and so on. Doing this would require using a helper table which keeps track of tag counts.
  
  
• Data repetition. Because of the same single-column tag storage, the tag data is repeated multiple times and may end up taking considerably more storage space than with the approach documented in the old post
  

Conclusion

So depending on your requirements, it may or may not be a good idea to go with a fulltext search for this. The advantages of a fulltext search is that it comes with a very extensive query language which you would otherwise have to write yourself and it also lets you use simpler queries and table structures. It does however need more storage space and makes counting tag amounts and such more difficult.

To learn more about the fulltext search, check out MySQL manual, Full-text search functions