MySQL - Impact of multiple column indexes misuse

  • HOME
  • >
  • NEWS
  • >
  • MySQL - Impact of multiple column indexes misuse
Published: , Last Updated:
April 30, 2020

👉 This post was initially written in 2008 and refered 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.

MySQL - Fulltext Indexes (MATH / AGAINST)
Related news posts that you may like
What's New in Focal Fossa Ubuntu 20.04 LTS?
Ubuntu 20.04 LTS is available for download. Find out some of the major security and performance improvements from this new distribution, including the introduction of WireGuard VPN, upgraded suite of software packages, faster boot time, etc.
What's New in GNU Bash 5?
Bash version 5 is generally available and comes with some important improvements and new features like BASH_ARGV0, EPOCHSECONDS, and EPOCHREALTIME.
Top 5 Machine Learning and Self-Healing Techniques used by SRE
Applying Machine Learning and Self-Healing techniques to the day operations of a production system has become common practices for most SREs. This post cover some real production use cases like automated failover, forecasting, anomalies detection, risk classification and so on.