Programster's Blog

Tutorials focusing on Linux, programming, and open-source

MySQL - Benchmarking Varchar vs Text

Today 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 times 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 times 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