Full Text Search In MySQL: 8 Things (3 Min) You Need To Know

In this tutorial, you'll learn how to use full text search in the MySQL database.

—
—
Video Transcript:
—
Hi guys, this is Abhi from Gokcedb. In this video, you're going to learn eight things you need to know about full-text searches in the MySQL database. Let's start by looking at the entity relationship diagram. I have 15 tables in my database right now but in this tutorial, we'll mostly be running queries on the property table.

Number one before you can run text searches on a column you first have to create a full-text index here. I'm using the alter table statement to create a full-text index on the property underscore subdivision column. Use show creates a table, table syntax to verify if a full-text key exists for a column or not.

Number two, you can run full-text searches in three modes. In natural language mode with query expansion and in boolean mode. Number three, say I wanted to select all the rows from the property table where the property underscore subdivision column has the keyword, green or Sunland.

To do that, I'll make use of the match function and the against function in the where clause. The column name goes in the match function and the search terms go in the against function. Here I'm using the natural language mode search modifier.

Number four the natural language mode is the default search modifier so you don't have to specify it. Number five, say I wanted to select all these subdivisions with the keyword sun in them in the boolean mode. To do that I'll write sun in the boolean mode in the against function.

You can also use the minus operator in the boolean mode to further reduce the results set. Here I'm using the minus acres keyword to exclude all the subdivisions with the keyword acres in them. Number six, to find all the subdivisions that start with the keyword p i e r use t i e r star in boolean mode.

Number seven is to select all the subdivisions that have the keyword acres and the keyword sun. Use plus acres plus the sun in boolean mode. Last but not the least number eight, is used with query expansion.

Search modifier to find all the relevant and related keywords to the search terms. Here, I use the sun as my search term but in the results set as you can see we found a lot more relevant and related keywords. There you have it.

Make sure you like, subscribe, and turn on the notification bell. Until next time.

ALTER TABLE table_name ADD FULLTEXT(column_name1, column_name2,…)
IN NATURAL LANGUAGE MODE, WITH QUERY EXPANSION, IN BOOLEAN MODE
'GREEN,SUNLAND' IN NATURAL LANGUAGE MODE
IN NATURAL LANGUAGE MODE search modifier by default
AGAINST('SUN -ACRES' IN BOOLEAN MODE)
AGAINST('TIER*' IN BOOLEAN MODE)
AGAINST('+ACRES +SUN' IN BOOLEAN MODE)
AGAINST('SUN' WITH QUERY EXPANSION)

[channaveerhakari]

Beautiful, short and crisp. Thank you very much.