Upgrading to MySQL 5
Wed, 20 June, 2007 – 9:34 pm
A couple of weeks ago I had some issues on a client's web site. One of my queries was taking 6 seconds to run, with 3 of those on the page the loading time was hitting around 20 seconds - far too long. With help from Khalid, he managed to reduce the query time to 0.05 seconds. Much better! However he also pointed out how I could have used a Subquery. I've used these a little in the past but haven't really had time to read much about them. Unfortunately we realised the server was running MySQL 3.23, far too old to be able to run subqueries, however as the server is a dedicated server for my client I figured I'd just ask for it to be upgraded.
What a mistake that was. First of all it took them over a week to get around to doing. Then they decided to do it late morning on a Monday, instead of during a quiet period. At first the site was down for a few minutes (the site gets over 4 visitors per minute on average, considering it's a UK site you could probably double that at least), which my client wasn't impressed with especially as they said it would take 20 seconds. Then they reverted back to the old version which I kept seeing for the next few hours. In the afternoon I came to try and login and realised they'd reset my root password so I couldn't gain access via phpMyAdmin.
However the fun and games started yesterday morning, the day I was planning to have off as we were packing to drive down to my parents where we're house sitting for just over 2 weeks whilst they're away. Apparently certain things weren't working. I spoke to the server tech support, requested they sort out my root password (which took an hour!) and also asked about the errors which we couldn't understand why. I then discovered if I put backwards quotes around the fieldnames in my SQL statements things were working, however a daunting task on the size of the site in question.
Again, Khalid came to my aid and after seeing the error he realised that one of the fieldnames in use was now a reserved word. Typical! Unfortunately the site sells secondhand products, so all products have a condition, and this was the word that was causing the problem. Of course this narrowed things down for me as I could open every file and do a quick search on the word and see what was there.
Whilst I'd requested the upgrade to at least version 4.1 (I wish it had been that, there'd have been no problems!), it still baffles me why they made the upgrade during a busy part of the day and a weekday too. Had they done this on Friday evening, it'd have given me the weekend to ensure everything was working. Of course I asked for it over a week ago and didn't realise they'd take around 10 days to go ahead with it, during a week when I didn't intend to be around too much!
