2

This may seem silly, but in trying to stick to my standard nomenclature when naming indexes in our DB, the latest one was a little lengthy, and like all things DB I wondered if it in any way could negatively affect performance. I'm just not sure where or how index names are actually stored or used vs the actual column data.

For example, is it advantageous to name an index epoid vs ecm_products_options_product_id?

The later has been closer to our standard and makes for easier reading and identification in the application layer.

5

In short: No.

There will be a very very small difference in parsing time for statements that specifically mention the index or generating output that mention the index, but this is so vanishingly small compared to all the other work that teh database engine it doing that it is simply noise - far too small to even reliably measure.

When the index name is not explicitly used then even that difference is gone: the index will be looked up by the columns it contains data for not by its name. If the engine needs to refer to he index directly it will do so by some internal ID - the name you know is essentially just for us humans.

Baring that in mind, especially the second point, long descriptive names are recommended. There is a caveat there though: if you make the name too long then it becomes unwieldy, and many tools will impose arbitrary limits on the length of identifiers. SQL Server for instance limits all names to 128 characters (if you get close to this limit you have probably gone too far!) but I've seen some tools (that were bad in other ways too...) fall over when they hit anything longer than 50. The mySQL documentation should tell you what the internal name length limits are for your circumstance.

3
  • What I figured, just thought I'd ask ;)
    – oucil
    Feb 4 '15 at 13:38
  • Maybe 1/1000 of a nanosecond ;) Feb 4 '15 at 14:01
  • Thanks for the expanded explanation David, didn't consider the portability of those indexes from engine to engine with regard to limits, or like you say 3rd party tools with arbitrary limits. Great points all around!
    – oucil
    Feb 4 '15 at 16:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.