MySQL - Benchmarking Varchar vs Text

A while ago, I was trying to analyze how much impact it would have if I was to use a very large VARCHAR compared to a TEXT type, to store the log messages in my database. These are log messages, and are not indexed. Instead, they are accompanied by a timestamp and an ID which are used for filtering/selection and archiving.

I built a tool in PHP to benchmark the performance difference between using the TEXT type and a very large VARCHAR.

How It Tests

• The tool creates a TEXT and a VARCHAR(21,000) based table on a master and slave database each.
• It then proceeds to insert a set number of logs into each of the tables and tracks how long this takes.
• It then proceeds to insert the same number of rows into the slave tables but with different content.
• It then runs the sync tool to sync the master to the slave and tracks how long that takes.

Testing Environment

• Deebian 9.4
• MySQL 5.7 with InnoDB tables
• 1 GB Ram
• 2 vCPUs

Results

When I ran the test set to use 10,000 logs I got the following results:

insert_logs_textfield: 9.482647895813 seconds
insert_logs_varchar: 9.5715599060059 seconds
sync_test_varchar: 25.566293954849 seconds
sync_test_text: 25.053405046463 seconds

When I ran with 100,000 logs, I got the following:

insert_logs_textfield: 90.345357179642 seconds
insert_logs_varchar: 91.518849372864 seconds
sync_test_varchar: 1482.8896179199 seconds
sync_test_text: 1460.9850070477 seconds

Conclusion

I couldn't reliably find any performance difference when using VARCHAR or TEXT types. I had expected VARCHAR to be noticeably faster as it stores the data in-line, but evidently I was wrong. This has further reinforced on me the importance of testing/benchmarking everything and assuming nothing. From now on, I will not worry about a performance impact of using the TEXT type on large tables and will purely focus on using the type that is most appropriate for the content. E.g. TEXT for a comment/message and VARCHAR for storing a user's email address.

Do You Feel I Missed Something?

If you feel that my benchmarking tool missed something and could be improved to simulate some sort of workload, please let me know in the comments or submit a pull request to the Github repository.

References

• Stack Overflow - MySQL: Large VARCHAR vs. TEXT?
• Percona Database Performance Blog - Blob Storage in Innodb