Feb 13 2008

MySQL – Impact of multiple column indexes misuse

Benchmarks, Tips Add comments

Here is a quick picture about the impact of MySQL indexes misuse. At work, our developers made a new release of their search engine using MySQL fulltext indexes, unfortunately they didn’t implement it correctly. The impact was a huge load on all our database servers. To find the trouble, I had to redirect the SQL search flow to a specific server and check for the slow queries then reproduce it with EXPLAIN. Don’t need a long time to find that the search query did an invalid usage of 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, else the index won’t be used by MySQL…

MySQL - Fulltext Indexes (MATH / AGAINST)

Digg This
Reddit This
Stumble Now!
Buzz This
Vote on DZone
Share on Facebook
Bookmark this on Delicious
Kick It on DotNetKicks.com
Shout it
Share on LinkedIn
Bookmark this on Technorati
Post on Twitter
Google Buzz (aka. Google Reader)
  • alex

    I made a couple graphs like this using RRD, but yours look way cooler. How did you make this?

  • alex

    I made a couple graphs like this using RRD, but yours look way cooler. How did you make this?

  • adam

    couldn’t figure out how to post on your mysql post you made 3 posts ago but the multiple index does actually work if you don’t specify all the columns. you just have to specify them in a particular order, which also means you need to select the columns to index in a particular order too. go about half way down this manual to see more of what i mean… http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

  • adam

    couldn’t figure out how to post on your mysql post you made 3 posts ago but the multiple index does actually work if you don’t specify all the columns. you just have to specify them in a particular order, which also means you need to select the columns to index in a particular order too. go about half way down this manual to see more of what i mean… http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

  • http://www.shell-tips.com Nicolas Brousse

    The post is mainly about full-text indexes. When you use full-text indexes for doing MATCH/AGAINST queries, your the MATCH part of your query require to use all the column used to define your FULLTEXT index.

  • http://nicolas.brousse.info Nicolas Brousse

    The post is mainly about full-text indexes. When you use full-text indexes for doing MATCH/AGAINST queries, your search (the MATCH part) require to use all the column used to define your FULLTEXT index.

blog comments powered by Disqus
WP Theme & Icons by N.Design Studio
Entries RSS Comments RSS Log in