MyISAM performance with text columns
I’ve now deployed with the new table structure. It turns out that one of the big things that was slugging performance was tables with ‘text’ columns. I’ve read around the subject and the general consensus seems to be that text and blob columns will both cause severe loss of performance.
As an example doing a count(*) on the message table has moved from 400 ms to 0 ms when I removed the text column. I’ve now shifted the text column into a separate message_detail table and am only querying on it when absolutely necessary.
Here’s what ’show status’ was showing before I made the changes:
Created_tmp_disk_tables 1839073
Created_tmp_tables 3880113
And here’s what the same command is showing now:
Created_tmp_disk_tables 1231
Created_tmp_tables 303910
So there has been an absolutely huge reduction in the proportion of temporary tables which are now being written to disk.
This has been a good start to the MySQL optimisation but there’s still more to do.