Looking for some clever sql strategies for dealing with a lookup table

I want to store the distance between different locations in a table.

CREATE TABLE `example` (
    `id` INT NOT NULL AUTO_INCREMENT ,
    `from` VARCHAR( 8 ) NOT NULL ,
    `to` VARCHAR( 8 ) NOT NULL ,
    `distance` DECIMAL( 6, 2 ) NOT NULL ,
    PRIMARY KEY ( `id` ) ,
    INDEX ( `from` , `to` )
 )

As a distance between two points is measured, it is inserted into the table. Getting the distance between 'from' and 'to' is obviously very easy. However the distance between 'to' and 'from' is exactly the same. I dont want to make another row just to switch the 'to' and 'from'. I would also prefer not to have to make a lookup table for this table, to accomplish this.

The sql to select the distance will be called quite a lot so it needs to be an efficient query.

I have simplified the table so don't worry about the places being varchars etc.

Anyone have any strategies they could recommend?

From stackoverflow

• Call the locations 'A' and 'B'. If A < B, then search for A in 'from' and B in 'to', otherwise, search for B in 'from' and A in 'to'. Same logic when inserting values.

  That way you only store each combination once, and it is fast to query.

  ae : exactly the kind of solution I was after - well done

• May I suggest something entirely different. Each point has coordinates relative to the same point, so the distances will be calculated when giving two points, and not saved in the DB.

  Now, if those are points on maps/Graph, then another solution is in place.

  Matt Howells : He didn't say that the locations had co-ordinates, nor that the distance between them is a straight line. If the locations are cities, and distance is the fastest road journey, they will certainly not be straight lines.

  Itay Moav : I mentioned graphs in my answer. Any way, the best way to store what he wants to store is not in a relational DB.