Profiling MySQL 5.6 InnoDB Fulltext index Performance

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

Sample SYSLOG data in table
syslog_data

Test 1
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;

Test 2
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.

Reference: https://dev.mysql.com/doc/refman/5.6/en/fulltext-natural-language.html

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s