We recently upgraded our Fedora Core server from 4.0.27 to 5.0.37 CS. In this process we also decided to change the character encoding on some of our tables from Latin1 to UTF 8. Wich is needed for a future release of our App. Problem that arose after the conversion was very slow “like” searches on large TEXT fields. factor 8-10 times slower then our previous version of MySQL on the same hardware.
After hours of debugging and testing we found that changing the encoding of the TEXT fields back to Latin1 (wich is acually not what we want but needed to be done) we get the performance back to what it was under 4.0.27. I understand that UTF-8 takes more bytes compared to Latin1 so that is probably where this comes from but we can’t seem to find a way to improve performance under UTF-8. Wich server-vars can be tweaked to gain some some speed here? we upped temp table sizes, switched MySQL tmp dir to an in memory (ram-disk) nothing seemes to help. So if anybody has a clue please let me know what to tweak.