{"id":104021,"date":"2024-10-25T03:56:00","date_gmt":"2024-10-25T03:56:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=104021"},"modified":"2025-06-30T11:33:20","modified_gmt":"2025-06-30T11:33:20","slug":"mysql-index-overviews-fulltext-b-tree-indexes","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/mysql\/mysql-index-overviews-fulltext-b-tree-indexes\/","title":{"rendered":"MySQL Index Overviews: FULLTEXT B-Tree Indexes"},"content":{"rendered":"\n

If you\u2019ve been following Simple Talk<\/a> for a while, you know that we\u2019re fans of databases and everything they involve. We\u2019re also big fans of database performance, too. And if you\u2019re a developer or a DBA, you surely know one of the primary answers to database performance \u2013 indexing! We\u2019ve already told you about indexes when we talked about multiple approaches to improve your query performance<\/a> and we\u2019ve already told you about indexing nuances<\/a> during some of our previous blog posts, too.<\/p>\n\n\n\n

We\u2019ve also walked you through the default \u2013 B-Tree \u2013 type of indexes in MySQL<\/a>. Now it\u2019s time to dive into its cousin \u2013 full-text indexing!<\/p>\n\n\n\n

What is Fulltext Indexing?<\/h2>\n\n\n\n

Full-text indexes are special \u2013 they\u2019re a type of index that enables fast and efficient searching through data using rather exotic search modes: in MySQL, those search modes include, but are not limited to the Boolean mode, natural language mode, and a search mode using query expansion. Some of the search modes made possible by using full-text indexes provide unique support for interpreting specific characters differently, searching for implied data, and other specific nuances.<\/p>\n\n\n\n

Full-text indexing works similarly across multiple database management systems and hasn\u2019t changed much during the years \u2013 take a look at Robert Sheldon’s blog on full-text indexes from more than two decades ago<\/a> and you will surely find something that\u2019s changed in your SQL Server installation, but you will also find a lot of things that remained the same; the same can be said about other database management systems.<\/p>\n\n\n\n

Full-text search capabilities are a means to an end: a means to add full-text search functionality onto their application or website or perform other complex searches through bigger data sets. They allow your search engine to act a little like Google does: it allows your database to return relevant results without you asking for them by using the expansion mode, allows you to search with fuzzy matching using the * character as the wildcard, allows you to search for exact matches by wrapping your query in double quotes, and has other unique capabilities.<\/p>\n\n\n\n

Grab the data used in this example from this link<\/a>, and it is included in the Appendix as well.<\/p>\n\n\n\n

Fulltext Indexes in MySQL<\/h2>\n\n\n\n

In MySQL, full-text indexes facilitate searches of 3 types:<\/p>\n\n\n

\n
    \n
  1. Searching using a natural language search mode<\/em> is made possible when no search mode is specified or when searches are conducted in the NATURAL LANGUAGE<\/code> MODE<\/code>.<\/li>\n\n\n\n
  2. Full-text searches with query expansion<\/em> are possible using assumed knowledge and can be defined if we define WITH QUERY EXPANSION<\/code> at the end of our query. Queries using such a search mode will run twice \u2013 once running the search in a default mode (the natural language mode), and the second time by adding the most relevant results from the first search and running the search again, thus coming back with an interesting result set in the end.<\/li>\n\n\n\n
  3. Boolean full-text searches<\/em> can be helpful if your application needs to search through data using various Boolean characters that have a varied meaning (\u201c~\u201d lowers the contribution of the value to the search results, \u201c-\u201c tells your database that the value mustn\u2019t be included, searching using double quotes only matches literal values, etc. Such searches necessitate the definition IN BOOLEAN MODE<\/code>.)<\/li>\n<\/ol>\n<\/div>\n\n\n

    It\u2019s also worth knowing that when full-text indexes are in use, certain characters are represented differently. For example, if our column doesn\u2019t have a full-text index on top of it and we\u2019re using a wildcard to power our searches, the wildcard needs to be represented as the \u201c%<\/code>\u201d character while if full-text indexes are in use the wildcard becomes the \u201c*<\/code>\u201d character and the \u201c%<\/code>\u201d character won\u2019t work.<\/p>\n\n\n\n

    These things considered, full-text indexes in MySQL can be defined on CHAR<\/code>, VARCHAR<\/code>, and TEXT<\/code> columns, and they should be defined like so:<\/p>\n\n\n\n

    CREATE TABLE `demo_table` (\n   `varchar_col` VARCHAR(60) DEFAULT \u2018\u2019, \n   `number_col` INT DEFAULT 0,\n   `other_col` TEXT,\n   -- And other columns\n   --FULLTEXT Added here\n   FULLTEXT varchar_idx(varchar_col)\n);<\/pre>\n\n\n\n
    If you want to define a full-text index on a table once it\u2019s already created, make use of an ordinary ALTER<\/code> clause with a FULLTEXT<\/code> definition:<\/p>\n\n\n\n
    ALTER TABLE `people` ADD FULLTEXT(first_name);<\/code><\/p>\n\n\n\n
    \"D:\\Misc\\Redgate\\Blogs\\June<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    Image 1 – Defining a Full-text Index when Modifying a Table<\/em><\/p>\n\n\n\n
    Fulltext indexes can also be created using the CREATE FULLTEXT INDEX<\/code> clause:<\/p>\n\n\n\n
    \"D:\\Misc\\Redgate\\Blogs\\June<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    Image 2 – Creating Fulltext Indexes with CREATE FULLTEXT INDEX<\/em><\/p>\n\n\n\n
    ALTER TABLE people ADD FULLTEXT(first_name);<\/code> would add a full-text index first_name<\/code> on the column first_name<\/code>, while CREATE FULLTEXT INDEX `ft_idx` ON `people`(first_name); <\/code>would add a full-text index ft_idx<\/code> on the first_name<\/code> column.<\/p>\n\n\n\n
    That means that if an index name is not specified, MySQL would create an index titled exactly as the column is titled. If an index name is specified, MySQL will create an index with a name that has been provided.<\/p>\n\n\n\n
    On a related note, also keep in mind that MySQL will warn you if you use duplicate names for your indexes but won\u2019t prevent you from creating a different type of index on the same column.<\/p>\n\n\n\n
    In other words, you can have an ordinary B-tree index and a full-text index on the same column because they would serve two distinct purposes<\/em>: a B-tree index on the first_name<\/code> column would satisfy queries like<\/p>\n\n\n\n
    SELECT * \nFROM `people` \nWHERE `first_name` = \u2018Demo\u2019;<\/pre>\n\n\n\n
    while a full-text index would satisfy columns like<\/p>\n\n\n\n
    SELECT * 
    FROM `people` 
    MATCH(first_name) AGAINST(\u201cDemo\u201d)<\/pre>\n\n\n\n
    Let\u2019s create a couple of indexes \u2013 you will see that MySQL will error out if we have a duplicate key name (that happens because indexes can be acted on depending on their names), but won\u2019t if we run multiple types of indexes:<\/p>\n\n\n\n
    \"D:\\Misc\\Redgate\\Blogs\\June<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    Image 3 – Multiple Indexes on a Column<\/em><\/p>\n\n\n\n
    We can confirm that one column has multiple indexes by running a simple DESCRIBE<\/code> query on our table (in this case, note the MUL<\/code> \u2013 multiple key<\/em> \u2013 definition below the Key<\/code> column. In our cases, that means that we\u2019ve defined multiple indexes):<\/p>\n\n\n\n
    \"D:\\Misc\\Redgate\\Blogs\\June<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    Image 4 – Multiple Indexes on a MySQL Table<\/em><\/p>\n\n\n\n
    Search Operations Using Fulltext Indexes in MySQL<\/h2>\n\n\n\n
    Cool, you now know how to build full-text indexes in MySQL. Time to search for data!<\/p>\n\n\n\n
    As I\u2019ve already told you before, full-text indexes in MySQL have a couple of search modes, these being the natural language search mode (this is the default search mode if no search mode is specified), Boolean search mode, and a mode with query expansion. Here\u2019s the syntax for full-text searches:<\/p>\n\n\n\n
    SELECT * \nFROM table_name\nWHERE MATCH(column_name) AGAINST(\u201cSearch Query\u201d [MODE]);<\/pre>\n\n\n\n
    In our case, we\u2019ve defined a full-text index on the first_name<\/code> column, which means that we can only run full-text search queries on that column. With that said, our query looks like so:<\/p>\n\n\n\n
    SELECT * \nFROM people \nWHERE MATCH(first_name) AGAINST(\u201cSearch Query\u201d [MODE]);<\/pre>\n\n\n\n
    Let\u2019s try it out:<\/p>\n\n\n\n
    \"D:\\Misc\\Redgate\\Blogs\\June<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    Image 5 – Running a Sample Query on a Fulltext Index<\/em><\/p>\n\n\n\n
    Specifying a search mode is not necessary unless you want to employ a specific full-text search mode. In that case, you would make use of one of three modes by defining them after the query.<\/p>\n\n\n\n
    Boolean Mode<\/h3>\n\n\n\n
    The purpose of a search in the Boolean mode is to search with \u201cfuzzy matching\u201d \u2013 such a search mode searches for given words and can act together with operators like less than (<<\/code>), more than (><\/code>), the plus (+<\/code>) and minus (-<\/code>) signs (they denote \u201cmore importance\u201d or \u201cless importance\u201d for a result set), double quotes to search for an exact<\/em> match, and have other characteristics as outlined in the documentation<\/a>.<\/p>\n\n\n\n
    For a search in the Boolean mode, everything would look like so:<\/p>\n\n\n\n
    SELECT * FROM people \nWHERE MATCH(first_name) \n          AGAINST(\u201cSearch Query\u201d IN BOOLEAN MODE);<\/pre>\n\n\n\n
    You can see this and a few other examples in the following output:<\/p>\n\n\n\n
    \"D:\\Misc\\Redgate\\Blogs\\June<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    Image 6 – Searches in Boolean Mode<\/em><\/p>\n\n\n\n
    Such a query will return results after searching for a query in the Boolean mode. Here, keep in mind that there are a couple of \u201cgotchas\u201d, for example, ordinary wildcard characters like \u201c%<\/code>\u201d and \u201c_<\/code>\u201d won\u2019t work with full-text indexes \u2013 your search won\u2019t error out, but you will get an empty result set:<\/p>\n\n\n\n
    \"D:\\Misc\\Redgate\\Blogs\\June<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    Image 7 – Fulltext Indexes and Wildcards<\/em><\/p>\n\n\n\n
    Boolean search is also named Boolean because it accepts Boolean symbols as part of search values. Have a look:<\/p>\n\n\n\n
    \"D:\\Misc\\Redgate\\Blogs\\June<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    Image 8 – Making Use of the Boolean Mode<\/em><\/p>\n\n\n\n
    Here we told our database to search for records containing \u201cApple<\/code>\u201d and \u201cjuice<\/code>.\u201d We had a record \u201cApples with juice<\/code>\u201d as well as \u201cApple<\/code>\u201d so it\u2019d picked those up \u2013 very interesting!<\/p>\n\n\n\n
    The Boolean operators available in the Boolean search mode are as follows:<\/p>\n\n\n
    \n