the devil is in the details when it comes to changing an index in a table that's already running with data in it in production from an operations standpoint.
Sure :-). Speaking about details - it wasn't "changing", but was "adding" a new index (with dropping an old one later).
How did this impact queries that were now joining on this newly re-indexed table? Did any of those queries get negatively impacted by it?
No observable degradation (after the index was created). In general - reading performance is usually unaffected by adding indexes; writing performance IS affected by adding indexes, but as actually it was one index added (on (PLAYERID,TIME)) and one dropped (on (PLAYERID)), - the difference was negligible. Actually, adding every single index makes things worse only by a negligible margin; it is if we'd follow advice of some wizard-which-advised-to-create-a-dozen-indexes-for-each-query-you-give-it - THEN we'd be in trouble.
How was the downtime accounted for?
Wait - any decent RDBMS allows to create indexes online (otherwise - it is plainly not suitable for operations). Sure, there is a slowdown while the index was created - but it wasn't too bad; but after index was created - everything went really_really smoothly (MUCH better than before - due to better locality, less cache poisoning etc. etc.).
I kind of wish there was more time spent on tweaking live production databases
This is a subject-which-is-rather-difficult-to-generalize (per-DBMS differences are significant, and I'm spoiled by DB/2 which allows to do lots of things pretty much seamlessly ;-)), but I still hope to cover certain basics of it in chapter 28 (tentative name "24x7 OLTP DB Maintenance 101"), stay tuned :-).
I love ITHare's stuff, but I feel like the devil is in the details when it comes to changing an index in a table that's already running with data in it in production from an operations standpoint. It was touched on lightly in the article:
How did this impact queries that were now joining on this newly re-indexed table? Did any of those queries get negatively impacted by it? How was the downtime accounted for?
I kind of wish there was more time spent on tweaking live production databases, instead of focusing on the mechanical aspects of how these changes were affecting specific queries' run times. Although I admit it was a great read either way.