For a long time, I’ve played with the idea of switching off ads for Carsurvey.org members. How Ads Really Work: Superfans and Noobs helped persuade me that it really was the right thing to do.

With immediate effect, anyone who has recently visited the Members Area of Carsurvey.org (the car site, not the airline site), will be able to view the main car site with almost all the ads removed. The members cookie lasts for 31 days. If you login to the Members Area at least once per month, the ads will stay removed, regardless of how many reviews or comments you rate.

The only exception to the ad removal is the UK used car search facility, which is doesn’t take up much screen real estate, and is subject to an agreement that I don’t want to break.

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.