Monthly Archives: June 2007

Random things I like

Smints: Annoying, these are becoming very difficult to find in the shops. Forced to order from the Internet, I had 48 boxes arrive this morning (free delivery if purchased in bulk)

Diet Coke with Lime: Much more pleasant than Cherry or Lemon Coke. Unfortunately it no longer seems to be sold in the UK 🙁

Techmeme: I’ve mentioned this before, but it’s now pretty much the first site I check every morning. Can’t recommend it highly enough. Mini-Techmeme is also high up the bookmarks list on my N95

Forza Motorsport 2: Ferrari, Porsche, Lamborghini, rock solid 60 frames per second, 720p, surround sound, the Nordschleife, Xbox 360 control pad, and traction control and stability control turned off. This is the best racing game ever made

MotoGP: I know F1 is supposed to be getting better, but I watched the F1 race from Indianapolis, and whilst entertaining, it wasn’t a patch on MotoGP. And this is from someone who runs a car website, and has never even ridden a motorbike

Colin McRae: DiRT: The best rally game for years. Not quite up to the standard of Forza 2, but it’s still brilliant fun. Can’t wait to unlock the full Pike’s Peak event, so I can have a go at replicating Climb Dance

Horslips – Dearg Doom: 70s Folk Prog genius. The silver gloves are a great touch…

Less ads for Carsurvey.org members

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.

MySQL foreign key data types

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.