I started writing this post to introduce database indexing to new team members and thought it was a good opportunity to flesh out my own knowledge and share what I learnt with others. Hope this helps someone out there.
Indexing is something that should be in the purview of the relevant DBMS and should rarely involve you as a programmer or as a user.
There are some things that have a greater on the performance than indexes. One of these is how you have designed the database in the first place. Over a thirty year period, I saw many examples of production databases that did not utilise smart thinking about the database design.
Another area that is problematic for performance is the design of the database queries themselves, especially if there are joins (of any kind) involved and there are large numbers of rows to be examined. Early in my database delvings, I found a particularly slow query (on the order of 1/2 hour execution times) was dramatically sped up (to the order of just seconds) by an accidental change in the specification of the relevant joins used. By this I mean, we removed a couple of the joins and then added them again. This lead me a specific hunt to understand join order and how it influences execution speed. Once one understood the limits on the query engine, one could then look for optimisations of the join order.
Another area is to not trust vendor recommendations until you have actually tested them thoroughly. A certain large database vendor had specific recommendations for query design that were problematic. A problem report was submitted about the specific "feature" and even after 3 more major releases, this "feature" still existed. On a relatively small database table of about 10 million records, the database vendor recommendation took about 10 minutes or so to run. By judicious coding of an external user interface, and not using the recommended "feature", the resultant query took less than 1/10 second to run to completion.
My recommendation to new teams members would be to have a good grasp of theory (so make sure that they are adequately trained) and then get them to do a set of little problems with a variety of gotchas to get them to think and to investigate what is happening. Unfortunately, I think this kind of activity went out in the late 80's.
