This test was performed on Centos 6.6 running MySQL 5.6.23.
I have a MySQL table which is populated by syslog messages via syslog-ng. I discussed how to configure syslog-ng to persist logs to a MySQL database in one of my earlier posts. I wanted to use this post to review some of the performance gains you will see by using a fulltext index on TEXT column in MySQL.
The syslog messages table layout looks like the following:
CREATE TABLE `logs` ( `host` varchar(32) DEFAULT NULL, `facility` varchar(10) DEFAULT NULL, `priority` varchar(10) DEFAULT NULL, `level` varchar(10) DEFAULT NULL, `tag` varchar(10) DEFAULT NULL, `datetime` datetime DEFAULT NULL, `program` varchar(15) DEFAULT NULL, `msg` text, `seq` bigint(20) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`seq`), KEY `date_host` (`datetime`,`host`), KEY `idx_msg` (`msg`(255)), FULLTEXT KEY `fulltext_msg` (`msg`) ) ENGINE=InnoDB AUTO_INCREMENT=56252 DEFAULT CHARSET=utf8
Total table record count: 56,274
For my first test a perform a query using the LIKE operator.
The query completes fairly quickly in 0.00057450 seconds. This is because the MySQL query optimizer is able to use the index.
SET profiling = 1; select * from logs where msg like '[e8725a%'; SHOW PROFILES;
I then performed the same test using the FULL TEX index.
This query finished slower in 0.00090200 seconds. Both the LIKE query and FULL TEXT index query examined 8 rows.
SET profiling = 1; SELECT * FROM logs WHERE MATCH (msg) AGAINST ('[e8725a'); SHOW PROFILES;
In my second test I performed a another LIKE query using a wildcard on both ends of the string. This resulted in the query examining 56,274 rows and returning 25 in 0.15842475 seconds. When using a wildcard on both ends of the string the query optimizer is unable to use the index and must do a full index scan.
SET profiling = 1; select * from logs where msg like '%/PolicyKit1%'; SHOW PROFILES;
When doing a FULL TEXT query the optimizer examined 25 rows and returned 25 row in 0.00083450 seconds. Resulting in a significantly faster query.
SET profiling = 1; SELECT * FROM logs WHERE MATCH (msg) AGAINST ('/PolicyKit1'); SHOW PROFILES;
In conclusion, FULL TEXT search can significantly increase the performance for queries run against large TEXT fields.