Matthew was the system administrator of a smallish warehousing company. His responsibility was to more or less keep the facility's computer systems working at a reasonable pace and ensure that nothing unexpected would bring the company's business to a screeching halt. Due to the typical resource constraints (money, time, qualified people), companies of this size frequently contract the development work for their internal software out to a third party. Moreover, as you might expect, the quality of those 'third parties' varies widely. Luckily, John, the third party responsible for his warehousing company's software was an industry veteran and was held in very high regard. You could say that there were those in the company thought he walked on water, but that would be unfair to the original. John's following was more devout.
One Monday morning, calls started pouring in complaining that the systems had slowed down markedly. As any good administrator would do, Matt checked to see if a number of potential culprits that had previously been identified and corralled in the past had popped up again. In this case, however, none of the usual suspects were at fault so, Matt reached out to John.
"Yes, I put some changes in over the weekend, but there is no way that they could have caused the problem. My tests were lightning fast and the system appeared to be running just fine when my work was done," John explained.
After a few minutes of begging and pleading, Matt was able to convince John to look at his changes one more time. "I'm sure it's not the cause, but I need to be able to report back that I've covered all of the potential areas," claimed Matt. Actually, this didn't come across that clearly to John...it's tough to enunciate when your tongue is planted against your cheek.
While waiting for John to report back, Matt decided to do some investigations of his own. Experience had indicated that John was reluctant to use source control systems. And by "reluctant", we mean "I don't need no stinkin' source control". Matt had not solved this problem so much as worked within it. He created a black-ops system that monitored the code directory used by John and when changes appeared, moved them into a real repository. You know, with back-ups and versions and stuff.
His spelunking of the repository quickly turned up something unusual: almost all of the SQL commands used in the application had been modified over the weekend. They referenced a 'new_id' field. A field which, despite its name, hadn't existed in the schema until just this past weekend. And, after Matt dug in a bit more, he found that the 'new_id' field, unsurprisingly, was not indexed.
Just then, mid-epiphany, the phone rang. It was John.
"Great news. I found the problem and have already taken care of it", he said.
Right, thought Matt. Because patching directly into production is exactly what should be done.
When Matt got off the phone, he checked into the code repository and found that the new_id field was no more. At least not within the codebase. It was still in the schema, just not accessed. There wasn't much Matt could do. Yes, it was a crude fix, but at least the application was back to its former speed.
A few weeks later, Matt had a meeting with the company owner.
"We have this major feature I want to get out, but we're having problems with that 'new_id' field John added."
"We could only search for really low values. All of the high ones took forever!"
"I saw the complaints. Wait...high ones??"
"John he did something to the table to fix it, something about reversing the order."
"Yeah, you'll have to talk to him. I don't know the specifics; I just know it works great now."
Hoping the owner was just confused, Matt called John to ask about the potential solution.
"Seems that 'new_id' field was getting too big and it was taking too long to get to the bottom of the data," explained John, "So I reversed the table and everything got really fast."
Matt's initial response was a look of apprehensive befuddlement.
"Yeah, I created a new table in 'new_id' reverse order, dropped the original, and renamed the copy. The change will be pushed out with the new update this weekend. Problem solved."
On Monday, Matt received a call that the application was still running slowly. With a gentle sigh, Matt asked them to hold on for a couple of moments. A quick fiddle with some DDL, and the 'new_id' field was indexed.
"How is it now?"
The sound of the keyboard carried across the phone wire. "Wow, this is really fast. And fixed quickly too. That John guy is amazing!"