|
I consider Indices to be premature optimisation. Moreover, they're very low level, potentially dangerous premature optimisation. Imho, they should be the very last trick you use to optimise. First, you try and figure out if you can reduce the number of repeated queries. Then you try and figure out if you can get rid of chunks of code that spawn lots of queries altogether, by tweaking the algorithm to do things differently, without needing that data. Then, finally, once you've used every trick to make it all faster, then you apply indices to speed up that handful of remaining queries. If you apply indices first, then you won't spot the other potential optimisations, and when your indices stop covering up for your poor coding it will be much later, and much harder to fix. |
Indices are standard way to achieve basic performance levels of a database. They may have their downsides but "potentially dangerous" is dramatically overstating the case. Furthermore, the dangers of premature optimization are about taking extra time or adding complexity to something that ultimately doesn't matter, not about using very basic features in a sane way.
The correct optimizations to make first are ones that make the biggest impact percentage-wise as well as being the most elegant in the code. Indices typically fit both categories very well. Unless you are doing a lot of stupid things, there's not going to be much lower-hanging fruit, but even if there is, after you apply sane indices that will be when your profiling will start to reveal the real interesting possibilities for optimization. The idea that indices are a good final optimization does not show much interest in real performance.