I’ve just made some changes that have massively improved the database performance of Carsurvey.org.

Back in 2000, when I added comments support to Carsurvey.org, I made the very silly decision to set the foreign key in the comments table to be a varchar (I thought I might want some flexibility above and beyond integer ids), and this varchar foreign key referenced an existing integer id field in the reviews table.

All seemed well, but for the last week I’ve been puzzling over why some of my join queries between comments and reviews were quite slow, and weren’t using the indices that I thought they should. After several days, I decided to change the comments foreign key datatype to integer, so it would match the reviews id datatype. Suddenly indices started being used in the way I was expecting, and I got a big performance increase.

So there are two lessons to be learnt here:

1. Unless you have a very very good reason, make your foreign key datatype match the datatype of the unique key in the reference table. It’s common sense for most people, but apparently not me circa 2000.

2. MySQL (version 4.1.20 anyway) doesn’t appear to optimise joins well where it has to match columns with different datatypes. This is presumably because of how it does type conversions.

Hopefully my confession will help anyone else who’s made a similar mistake, and is trying to work out why their query isn’t using the index they expect.

I’ve just spent a couple of days optimising some of the database code on Carsurvey.org. Performance wasn’t becoming an significant issue, but I had bee in my bonnet about some of the existing code, where I knew there were some simple improvements I could implement. Specifically:

Replacing lots of nasty PHP mysql_result and mysql_num_rows calls, with more efficient mysql_fetch_array loops.

Using the slow query log with its log-queries-not-using-indexes option, to find some a couple of stray queries that were doing full table scans, which were quickly addressed by adding suitable indices.

Below the main text of each review on Carsurvey.org, MotorcycleSurvey.com, and MobilePhoneSurvey.com, I’ve added a “More XXXX YYYY reviews” link. For example, a More Ford Focus reviews link on all Ford Focus review pages.

There are similar links on other parts of the page, but for visitors who arrive from a search engine, and aren’t familiar with the structure of the site, this will hopefully make it easier for them to find additional reviews.

Related to this change, the read comments link has been moved across to the right of the page, and the spelling and deletion links have been shortened, to make the wording more direct.

If this change works, I’ll look at replicating this sort of change for other pages on the site – for example, putting a link to All Ford Reviews at the bottom of a page listing Ford Focus reviews.

The Carsurvey.org webserver is now up and running again.

I’m letting it bed in for a few hours before I turn on the addition of new reviews and comments, but things are looking good so far.

Apologies once again for the downtime.

The server seems to have left the world of the living, and at long last, the hard drives are being swapped into a new server. Hopefully (crosses fingers and toes) Carsurvey.org will be back up shortly.

In the medium term, I’ll be moving to another web host, as I have no desire to repeat the experience of the last few days ever again.

The Carsurvey.org web server is still stuck in support hell. I’m pretty sure it’s a hardware fault, but no one at the hosting company (The Planet) seems to have got to the source of the problem. In the medium term, I’m definitely going to move hosts, as my recent experiences support at The Planet have been very poor.

Sorry for the long wait, and if things aren’t resolved soon, I’ll just rent a server from someone else, and rebuild Carsurvey.org from my backups. That would take a day or so, plus the time needed for the DNS change to take effect, so I’m still going to give The Planet one last chance to fix the problem.

Unfortunately it’s still broken. It took 11 hours for someone to look at it, and they failed to resolve the problem (they just rebooted the server). They’re having another look now, so hopefully there will be some progress soon.

I’ve got places to be today, but I’ll be keeping up to date via my N800 (over GPRS, given that I sent my N95 back for replacement yesterday. My sense of timing is terrible).

Once the dust has settled on this, I’m going to be looking into other web hosts, as this is my second major problem within a year that hasn’t been handled very well.

Once again, I apologise for the problems, and fingers crossed, this will soon be just an unpleasant memory.

The Carsurvey.org webserver started having problems earlier today; it was frequently rebooting itself for no obvious reason.

I’ve raised a trouble ticket with hosting company, and while I’m waiting for them to investigate the problem, I’ve managed to disabled the posting of new reviews and comments, and the members area, so at least no one can waste their time posting to an unstable server.

As soon as I realised that the server wasn’t well, I grabbed a backup of the database (my last known good backup was several days old), and I’ve been able to restore this new backup onto my developement server (not publicly accessible), so if the problem turns out to be serious, very little data will be lost.

Annoying that these things always happen at a really inconvenient time. I fancied a night in front of the TV with a cold glass of white wine, but instead I’ve got a night in front of the computer with as many caffeinated beverages as I can stomach 🙁

Anyway, apologies for the downtime, and hopefully things will be back up soon. Once things are back up, I’ll probably leave keep review and comment posting turned off for a while, just to make sure things are stable.

For Carsurvey.org and related sites, I’ve just revised the pages for adding new comments. Basically:

• More concise guidelines, so less text to read
• Fixed a few silly bugs
• With the reduced guidelines, I’ve been able to reduce the number of pages from 4 to 3

Less pages and text should hopefully make for a more pleasant experience. Next week I plan to make similar changes to the pages for adding new reviews

Carsurvey.org (and related sites) have just had some minor (in code terms) changes made to them, which hopefully will make the sites more pleasant to use.

Firstly, I’ve disabled the filtering of reviews into well written and other reviews. The filtering was based on the votes by visitors and members, and while the data was usually good, there were lots of cases where the voting didn’t seem to correlate with review quality. The filtering of reviews into two pages (well written and other) also complicated the navigation, so I’ve decided that the trade off isn’t worth it, and I’ve disabled filtering in favour of simplicity.

Related to the filtering issue, is the fact that on pages listing reviews for a specific model, the reviews were ordered by year of manufacture (if appropriate), and then by date added. The date added order wasn’t obvious, and anyway, there’s a sidebar and RSS feed for people who want to see the very latest reviews.

Although the ordering by year of manufacturer still remains, I’ve now replaced the date added order by a word count order. The longest reviews will appear at the top of the page or year grouping. I personally prefer the new ordering, but please let me know if you think otherwise.

I’ve also made the sites slightly more aggressive about splitting reviews into pages for each year, as some of the pages were getting a bit too long for my taste.