Last updated: 👉 This post was initially written in 2008 and referred to specific software versions. When tunning your system, always consider which version you are running. Information below may be outdated. Use it at your own risk.
This post shows the impact in a production system of imporperly using MySQL indexes. At work, our developers made a new release for their search engine using MySQL fulltext indexes, unfortunately they didn’t implement it correctly.
The impact was a huge increase of cpu load on all our database servers. To find the root cause, I had to redirect the SQL search flow to a specific server and check for the slow queries then reproduce it with EXPLAIN. It didn’t require a long time to find that the search query was incorrectly using the fulltext index and the “Match / Against” syntax. In fact, the fulltext index was a multiple column fulltext index, in such case you have to specify ALL the column present in your index, otherwise the index won’t be used by MySQL.
