I’ve never been a very big fan of MyISAM; I would argue that in most situations, any possible advantages to using MyISAM are far outweighed by the potential disadvantages and the strengths of InnoDB. However, up until MySQL 5.6, MyISAM was the only storage engine with support for full-text search (FTS). And I’ve encountered many customers for whom the prudent move would be a migration to InnoDB, but due to their use of MyISAM FTS, the idea of a complete or partial migration was, for one reason or another, an impractical solution. So, when FTS for InnoDB was first announced, I thought this might end up being the magic bullet that would help these sorts of customers realize all of the benefits that have been engineered into InnoDB over the past few years and still keep their FTS capability without having to make any significant code changes.
Unfortunately, I think that hope may be premature. While it is true that InnoDB full-text search in MySQL 5.6 (part 1) in MySQL 5.6 is syntactically identical to MyISAM full-text search, in the sense that the SQL required to run a MATCH .. AGAINST is the same (modulo any new features introduced with InnoDB full-text search), that’s largely where the similarities end.
NOTE 1: I was originally planning to cover everything I wanted to discuss with respect to InnoDB full-text search in this one post, but I think there’s a lot of interesting stuff here, so I will break it into three pieces instead. The first part (this one) will be a very quick overview of FTS in InnoDB and some observations that I’ve made while getting it configured. The second part will compare query results between MyISAM FTS and InnoDB FTS over the same data sets, and then finally in the third installment, we’ll look at query performance. In the event that a new release of 5.6 appears between now and part 3, I’ll also revisit some of the “quirks” from parts 1 and 2 to see if the behavior has changed.
NOTE 2: For purposes of this discussion, I used two separate data sets. The first one is a set of about 8K very SEO-stuffed web pages, wherein the document title is the page title, and the document body is the HTML-tag-stripped body of the page. We’ll call this data set “SEO” – it’s about 20MB of actual data. The other one is a set of about 790K directory records, each one containing the name, address, and some other public-records-type information about each person. We’ll call this data set “DIR”, and it’s about 155MB of actual data.
NOTE 3: Also, keep in mind that I used the community editions of MySQL 5.5.30 and MySQL 5.6.10 with no tuning whatsoever (with one exception that I’ll explain below) – the idea behind this investigation wasn’t to find out how to make InnoDB FTS blazingly-fast, but simply to get a sense of how it works compared to traditional MyISAM FTS. We’ll get to performance in the third installment. For now, the important number here is to note that the InnoDB buffer pool for my 5.6 instance is 128MB – smaller than the size of my DIR data.
So, with all of that out of the way, let’s get to it.
Here is our basic table definition for the DIR dataset. The table for the SEO dataset looks identical, except that we replace “full_name” with a VARCHAR(255) “title” and “details” with a TEXT “body”.
1 2 3 4 5 6 7 | CREATE TABLE `dir_test_innodb` ( `id` int(10) unsigned NOT NULL, `full_name` varchar(100) DEFAULT NULL, `details` text, PRIMARY KEY (`id`), FULLTEXT KEY `full_name` (`full_name`,`details`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
We also have identical tables created in 5.5.30 where, of course, the only difference is that the engine is MyISAM rather than InnoDB. Loading the data was done via a simple Perl script, inserting one row at a time with AutoCommit enabled – remember, the focus here isn’t on performance just yet.
Having loaded the data, the first thing we notice is that there are a lot of “new” InnoDB tablespace files in our database directory:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | -rw-rw----. 1 mysql mysql 8632 Feb 20 15:54 dir_test_innodb.frm -rw-rw----. 1 mysql mysql 213909504 Feb 20 15:55 dir_test_innodb.ibd -rw-rw----. 1 mysql mysql 98304 Feb 20 16:09 FTS_00000000000000ad_0000000000000153_DOC_ID.ibd -rw-rw----. 1 mysql mysql 98304 Feb 20 16:09 FTS_00000000000000ad_ADDED.ibd -rw-rw----. 1 mysql mysql 98304 Feb 20 16:09 FTS_00000000000000ad_BEING_DELETED_CACHE.ibd -rw-rw----. 1 mysql mysql 98304 Feb 20 16:09 FTS_00000000000000ad_BEING_DELETED.ibd -rw-rw----. 1 mysql mysql 98304 Feb 20 16:09 FTS_00000000000000ad_CONFIG.ibd -rw-rw----. 1 mysql mysql 98304 Feb 20 16:09 FTS_00000000000000ad_DELETED_CACHE.ibd -rw-rw----. 1 mysql mysql 98304 Feb 20 16:09 FTS_00000000000000ad_DELETED.ibd -rw-rw----. 1 mysql mysql 98304 Feb 20 16:09 FTS_00000000000000ad_STOPWORDS.ibd -rw-rw----. 1 mysql mysql 98304 Feb 20 15:54 FTS_0000000000000114_0000000000000144_DOC_ID.ibd -rw-rw----. 1 mysql mysql 98304 Feb 20 15:54 FTS_0000000000000114_ADDED.ibd -rw-rw----. 1 mysql mysql 98304 Feb 20 15:54 FTS_0000000000000114_BEING_DELETED_CACHE.ibd -rw-rw----. 1 mysql mysql 98304 Feb 20 15:55 FTS_0000000000000114_BEING_DELETED.ibd -rw-rw----. 1 mysql mysql 98304 Feb 20 15:55 FTS_0000000000000114_CONFIG.ibd -rw-rw----. 1 mysql mysql 98304 Feb 20 15:54 FTS_0000000000000114_DELETED_CACHE.ibd -rw-rw----. 1 mysql mysql 98304 Feb 20 15:55 FTS_0000000000000114_DELETED.ibd -rw-rw----. 1 mysql mysql 98304 Feb 20 15:54 FTS_0000000000000114_STOPWORDS.ibd -rw-rw----. 1 mysql mysql 8618 Feb 20 16:09 seo_test_innodb.frm -rw-rw----. 1 mysql mysql 37748736 Feb 20 16:29 seo_test_innodb.ibd |
By comparison, this is what we see on the MyISAM side:
1 2 3 4 5 6 | -rw-rw----. 1 mysql mysql 8632 Feb 19 17:17 dir_test_myisam.frm -rw-rw----. 1 mysql mysql 155011048 Feb 19 17:17 dir_test_myisam.MYD -rw-rw----. 1 mysql mysql 153956352 Feb 19 17:18 dir_test_myisam.MYI -rw-rw----. 1 mysql mysql 8618 Feb 20 16:11 seo_test_myisam.frm -rw-rw----. 1 mysql mysql 21561096 Feb 20 16:11 seo_test_myisam.MYD -rw-rw----. 1 mysql mysql 14766080 Feb 20 16:11 seo_test_myisam.MYI |
I also observed that if I simply load the data into an InnoDB table that has never had a full-text index on it, and then I create one, the following warning is generated:
1 2 3 4 5 6 7 8 9 10 | mysql> alter table dir_test_innodb ADD FULLTEXT KEY (full_name, details); Query OK, 0 rows affected, 1 warning (39.73 sec) Records: 0 Duplicates: 0 Warnings: 1 mysql> show warnings; +---------+------+--------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------+ | Warning | 124 | InnoDB rebuilding table to add column FTS_DOC_ID | +---------+------+--------------------------------------------------+ |
This doesn’t make a lot of sense to me. Why does InnoDB need to add a hidden column (similar to GEN_CLUST_INDEX when you don’t define a PRIMARY KEY, I assume) when I already have an INT UNSIGNED PK that should suffice as any sort of document ID ? As it turns out, if you create a column called FTS_DOC_ID which is a BIGINT UNSIGNED NOT NULL with a unique index on it, your table doesn’t need to be rebuilt. The most important item to note here – FTS_DOC_ID must be spelled and specified exactly that way – IN ALL CAPS. If you try “fts_doc_id” or any other mixture of lettercase, you’ll get an error:
1 2 | mysql> CREATE TABLE dir_test_innodb4 (fts_doc_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY); ERROR 1166 (42000): Incorrect column name 'fts_doc_id' |
Various points in the documentation mention the notion of a “document ID” that “might reflect the value of an ID column that you defined for the underlying table, or it can be a sequence value generated by InnoDB when the table does not contain a suitable column,” but there are only a handful of references to FTS_DOC_ID found when searching the MySQL 5.6 manual, and the only page which appears to suggest how using an explicitly-defined column is done is this one, which discusses improving bulk insert performance. At the very bottom, the page claims that you can speed up bulk loading into an InnoDB FT index by declaring a column called FTS_DOC_ID at table creation time of type BIGINT UNSIGNED NOT NULL with a unique index on it, loading your data, and then creating the FT index after the data is loaded.
One obvious problem wih those instructions is that if you define a column and a unique key as they suggest, your data won’t load due to unique key constraint violations unless you also do something to provide some sort of sequence value for that column, whether as an auto_increment value or via some other means, but the bit that troubles me further is the introduction of a column-level case-sensitivity requirement that only seems to actually matter at table creation time. Once I’ve got a table with an explicit FTS_DOC_ID column, however, MySQL apparently has no problem with either of the following statements:
1 2 | mysql> insert into dir_test_innodb3 (fts_doc_id, full_name) values (1, 'john smith'); mysql> select * from dir_test_innodb3 where fts_doc_id=1; |
Philosophically, I find that kind of behavior unsettling. I don’t like case-sensitivity in my table or column names to begin with (I may be one of the few people that likes lower_case_table_names = 1 in /etc/my.cnf), but I think it’s even worse that the case-sensitivity only matters some of the time. That strikes me as a good recipe for DBA frustration.
Now, let’s return to all of those FTS_*.ibd files. What, exactly, are they? In short, the _CONFIG.ibd file contains configuration info about the FT index (the same sort of configuration data that can be queried out of the I_S.INNODB_FT_CONFIG table, as I’ll discuss momentarily), and the others contain document IDs of new rows that are added to or removed from the table and which need to be merged back into or removed from the main index. I’m not entirely sure about the _STOPWORDS.ibd file just yet; I thought it might be somehow related to a custom stopwords table, but that doesn’t seem to be the case (or at least not in the way that I had thought), so I will need to look through the source to figure out what’s going on there.
In any case, for each new FULLTEXT KEY that you create, you’ll get a corresponding FTS_*_DOC_ID.ibd file (but none of the others), and if you drop a FT index, the its corresponding FTS_*_DOC_ID.ibd file will also be removed. HOWEVER, even if you drop all of the FT indexes for a given table, you’re still left with all of the other FTS_*.ibd files, and it appears that the only way to get rid of them is to actually rebuild the table.
1 2 3 4 5 6 7 | -rw-rw----. 1 mysql mysql 98304 Feb 20 18:57 FTS_0000000000000025_ADDED.ibd -rw-rw----. 1 mysql mysql 98304 Feb 20 18:57 FTS_0000000000000025_BEING_DELETED_CACHE.ibd -rw-rw----. 1 mysql mysql 98304 Feb 20 18:57 FTS_0000000000000025_BEING_DELETED.ibd -rw-rw----. 1 mysql mysql 98304 Feb 20 19:07 FTS_0000000000000025_CONFIG.ibd -rw-rw----. 1 mysql mysql 98304 Feb 20 18:57 FTS_0000000000000025_DELETED_CACHE.ibd -rw-rw----. 1 mysql mysql 98304 Feb 20 18:57 FTS_0000000000000025_DELETED.ibd -rw-rw----. 1 mysql mysql 98304 Feb 20 18:57 FTS_0000000000000025_STOPWORDS.ibd |
Also, while we’re on the subject of adding and dropping FT indexes, it’s entirely possible to DROP multiple FT indexes with InnoDB in the same ALTER TABLE statement, but it’s not possible to CREATE more than one at a time. If you try it, this is what happens:
1 2 3 | mysql> alter table dir_test_innodb ADD FULLTEXT KEY (full_name, details), ADD FULLTEXT KEY (details); ERROR 1795 (HY000): InnoDB presently supports one FULLTEXT index creation at a time |
That’s an odd limitation. Do it as two separate ALTER statements, and it appears to work fine.
But here’s where things start to get even weirder. According to the documentation, if we specify the name of a table that has a FT index for the global variable innodb_ft_aux_table, we should be able to get some statistics about the FT indexes on that table by querying the various I_S.INNODB_FT_* tables. In particular, the INNODB_FT_CONFIG table is supposed to “display metadata about the FULLTEXT index and associated processing for an InnoDB table.” The documentation also tells us that we can keep our FT indexes up to date by setting innodb_optimize_fulltext_only = 1 and then running OPTIMIZE TABLE, and that we might have to run OPTIMIZE TABLE multiple times if we’ve had a lot of changes to the table.
This all sounds pretty good, in theory, but at least some part of it doesn’t seem to work. First, let’s check the stats immediately after setting these variables, and then let’s push some additional data into the table, run an OPTIMIZE or two, delete some data, and see what happens:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 | mysql> set global innodb_ft_aux_table='test/dir_test_innodb'; mysql> set global innodb_optimize_fulltext_only=1; mysql> select * from information_schema.innodb_ft_config; +---------------------------+--------+ | KEY | VALUE | +---------------------------+--------+ | optimize_checkpoint_limit | 180 | | synced_doc_id | 787625 | | last_optimized_word | | | deleted_doc_count | 0 | | total_word_count | | | optimize_start_time | | | optimize_end_time | | | stopword_table_name | | | use_stopword | 1 | | table_state | 0 | +---------------------------+--------+ 10 rows in set (0.00 sec) mysql> insert into dir_test_innodb (full_name, details) SELECT reverse(full_name), details FROM dir_test_innodb WHERE id < 500000; Query OK, 245051 rows affected (8 min 13.13 sec) Records: 245051 Duplicates: 0 Warnings: 0 mysql> select * from information_schema.innodb_ft_config; +---------------------------+---------+ | KEY | VALUE | +---------------------------+---------+ | optimize_checkpoint_limit | 180 | | synced_doc_id | 1028261 | | last_optimized_word | | | deleted_doc_count | 0 | | total_word_count | | | optimize_start_time | | | optimize_end_time | | | stopword_table_name | | | use_stopword | 1 | | table_state | 0 | +---------------------------+---------+ 10 rows in set (0.02 sec) mysql> optimize table dir_test_innodb; +----------------------+----------+----------+----------+ | Table | Op | Msg_type | Msg_text | +----------------------+----------+----------+----------+ | test.dir_test_innodb | optimize | status | OK | +----------------------+----------+----------+----------+ 1 row in set (4.60 sec) mysql> select * from information_schema.innodb_ft_config; +---------------------------+---------+ | KEY | VALUE | +---------------------------+---------+ | optimize_checkpoint_limit | 180 | | synced_doc_id | 1032677 | | last_optimized_word | | | deleted_doc_count | 0 | | total_word_count | | | optimize_start_time | | | optimize_end_time | | | stopword_table_name | | | use_stopword | 1 | | table_state | 0 | +---------------------------+---------+ 10 rows in set (0.00 sec) mysql> delete from dir_test_innodb LIMIT 200000; Query OK, 200000 rows affected (8.65 sec) mysql> optimize table dir_test_innodb; +----------------------+----------+----------+----------+ | Table | Op | Msg_type | Msg_text | +----------------------+----------+----------+----------+ | test.dir_test_innodb | optimize | status | OK | +----------------------+----------+----------+----------+ 1 row in set (8.44 sec) mysql> select * from information_schema.innodb_ft_config; +---------------------------+---------+ | KEY | VALUE | +---------------------------+---------+ | optimize_checkpoint_limit | 180 | | synced_doc_id | 1032677 | | last_optimized_word | | | deleted_doc_count | 0 | | total_word_count | | | optimize_start_time | | | optimize_end_time | | | stopword_table_name | | | use_stopword | 1 | | table_state | 0 | +---------------------------+---------+ |
I ran OPTIMIZE TABLE several more times, and each execution took between 6 and 8 seconds, but the output of the query against I_S.innodb_ft_config never changed, so it seems like at least some of the diagnostic output isn’t working quite right. Intuitively, I would expect some changes in total_word_count, or optimize_(start|end)_time, and the like. However, if I check some of the other I_S tables, I do find that the number of rows in I_S.innodb_ft_index_table is changing, so it’s pretty clear that I do actually have a FT index available.
At the start of this post, I mentioned that I did make one configuration change to the default InnoDB settings for MySQL 5.6, and that was to change innodb_ft_min_token_size from the default of 3 to a value of 4 so that it would be identical to the MyISAM default. After all, the (naive?) hope here is that when I run an FTS query against both MyISAM and InnoDB I will get back the same results; if this equivalence doesn’t hold, then as a consultant, I might have a hard time recommending this feature to my customers, and as an end user, I might have a hard time using the feature at all, because it could completely alter the behavior of my application unless I also make a nontrivial number of code changes.
In part 2 of this series, we’ll reload and reset our SEO and DIR data sets back to their initial states, run some queries, and compare the output. Stay tuned, it gets rather curious.
Ernie, I think there could be more documentation on the Doc ID concept. The reason that this is vital important is from the design. When deleting a document, InnoDB Fulltext index does not delete its tokenized words from corresponding auxiliary tables (to avoid too many tables updates), instead, it records the deleted Doc ID. In such way, it can’t use the primary key, because the same primary key can be deleted and re-inserted, but with different documents
And regarding why the config.ibd etc. are still there when you drop the indexes is due to the same reason, that is, to avoid a table reorg to remove the hidden column. And we have the basic auxilliary tables remain to maintain this Doc ID, as well as avoid another table rebuild if user creates fulltext index again.
In terms of information_schema.innodb_ft_config table, it is really an internal config table, not for the purpose of statistics (that needs to be corrected in documentation).
I am new to MySQL. Can you explain how to change the settings for innodb_ft_min_token_size? I need to be able to search 2-digit codes for states in the USA, and it is currently skipping over them because they are too short.