An un-indexed column, 1,800,000 rows, and a one order for a large quantity of one item: the makings of a server meltdown.
I've been making lots of changes to the site, and the client was happy with me (finally!) so this was a problem I really didn't want. Every once in a while the server would just stop responding, for no apparent reason.
It was very confusing. The server admin was telling me that ColdFusion was using more and more CPU percentage until Jrun would restart, but I wasn't seeing that it was restarting. There just didn't seem to be any consistency with the slow down. And the entire time, my client is getting more and more frustrated.
This one was easy to solve. Using Microsoft's Log Parser 2.2, I generated a list of the longest running page of the day. One page in the administrative back-end was taking about 30 seconds to load. Running "Explain" on each of the queries showed me one query that was doing three sub-queries on an un-indexed field. MySQL was checking 1,800,000 rows on each sub-query, multiplied by the 275 rows returned totals 1,485,000,000 rows checked. Thats an awful lot of rows!
Of course, a simple index cured that. This field should've had an index from day 1. It was just waiting for the right set of circumstances to come along to manifest the need.
Item of the day: Stone Forest Vessel Pedestal C01-P1 BL - Bath Vanity
