
Database performance: If you're not first, you're last - rohland
https://dev.to/rohland/database-performance-if-you-re-not-first-you-re-last-n4
======
rohland
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.

~~~
oldandtired
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.

