2

I had never heard of covering indexes before and just came across them. I was reading this page on them and it says..

"A covering index can dramatically speed up data retrieval but may itself be large due to the additional keys, which slow down data insertion & update. To reduce such index size, some systems allow non-key fields to be included in the index. Non-key fields are not themselves part of the index ordering but only included at the leaf level, allowing for a covering index with less overall index size."

So my question is how do you know if your system allows non-key fields to be included in the index?

senthilbp
  • 807
  • 1
  • 9
  • 16
Brett
  • 19,449
  • 54
  • 157
  • 290
  • 4
    *how do you know if your system allows non-key fields to be included in the index*: You check the documentation for your system. However to speed up your search MySQL does not support non-key columns to be specified within indexes. [This answer](http://stackoverflow.com/a/13387903/1048425) gives a possible work around. – GarethD Dec 31 '12 at 11:46
  • MySQL does not support this. @GarethD you should add that as an answer so that he could accept that. – Imre L Dec 31 '12 at 11:52
  • 1
    @GarethD Yeah, was aware of that option, but wanted to avoid the overhead of large index size because of it. – Brett Dec 31 '12 at 11:53
  • @Brett I'm inclined to agree with you there. As I've mentioned in my answer I can't imagine a dataset where the performance gain of including many more columns than necessary in an index would out weigh the performance hit on inserts/updates and the overall size of the index. – GarethD Dec 31 '12 at 12:10
  • @GarethD Yep, agree. Guess I'll have to do without haha – Brett Dec 31 '12 at 12:15

1 Answers1

3

MySQL does not (currently) support non-Key columns. For other DMBS you will need to check the reference manual.

A similar question has been asked and answered here. However since the performance improvement gained by using covering indexes is generally greater for non-selective queries returning a large number of rows, I can't envisage the solution of just including the extra columns within the index itself ever offering a performance improvement. However, there may be scenarios I am not thinking of, and yours may be one of these, so as always when looking for performance improvement, testing, execution plans and IO statistics will tell you far more than my conjecture!

Community
  • 1
  • 1
GarethD
  • 68,045
  • 10
  • 83
  • 123