MySQL uses only part of the key when I add an OR

[taras.di]

I'm trying to understand why MySQL isn't using all parts of the primary
key when I add in an OR statement in the query.

I have a people database, and a distances database. The people database
lists a person's name and their location, the distances database
contains the distance between any two points in the country. There are
an roughly 15000^2 entries in the distances table, and currently about
100 entries in the people table. The schemas are as follows:

table distances
(
sourceId smallint unsigned not null,
destinationId smallint unsigned not null,
distance smallint unsigned not null, -- distance in kilometres
primary key(sourceId, destinationId)
) ENGINE = MYISAM ROW_FORMAT = FIXED PACK_KEYS = 1;

table people
(
personId int unsigned not null auto_increment primary key,
personName varchar(50) not null
) ENGINE = MYISAM;

The following queries aim at finding all people located within some
user specified radius centred around some user specified point. The
user has the option of specifying multiple points, eg: find me all
people located within (100 kms of point
A) or (300kms of point B).

In the next query, with the explain output attached, the user has
entered one centre point which he wishes to search around.

explain select * from people,distances as d1 where
people.locationId = d1.sourceId and d1.destinationId = 2 and
d1.distance <= 1000

+----+-------------+-------+--------+---------------+---------+---------+-----------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-----------------------------+------+-------------+
| 1 | SIMPLE | people | ALL | sourceId | NULL | NULL | NULL | 103 | |
| 1 | SIMPLE | d1 | eq_ref | PRIMARY | PRIMARY | 4 |
db.people.locationId,const | 1 | Using where |
+----+-------------+-------+--------+---------------+---------+---------+-----------------------------+------+-------------+

SQL is using both parts of the key, as expected.

However, when the user specifies two centre points he wishes to search
around:

explain select * from people,distances as d1 where
people.locationId = d1.destinationId and d1.sourceId = 2 and
d1.distance <= 1000
or
people.locationId = d1.destinationId and d1.sourceId = 3 and
d1.distance <= 2000

+----+-------------+-------+-------+---------------+----------+---------+-------------------------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
+----+-------------+-------+-------+---------------+----------+---------+-------------------------+-------+-------------+
| 1 | SIMPLE | d1 | range | PRIMARY | PRIMARY | 2 | NULL | 30731 |
Using where |
| 1 | SIMPLE | people | ref | sourceId | sourceId | 2 |
db.d1.destinationId | 11 | Using where |
+----+-------------+-------+-------+---------------+----------+---------+-------------------------+-------+-------------+

The optimiser chooses to join people to d1. It can't use the
people.locationId constraint in scanning the d1 table, so it only uses
the constant constraint. (Side note: why does the 'ref' column read
'NULL' - isn't mysql using the constants
2 & 3 and matching them to the key?)

In an effort to get mysql to reverse the join order (and thus make it
possible to use both parts of the keys), I used straight_join:
explain select straight_join * from people,distances as d1 where
people.locationId = d1.destinationId and d1.sourceId = 2 and
d1.distance <= 1000
or
people.locationId = d1.destinationId and d1.sourceId = 3 and
d1.distance <= 2000
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+
| 1 | SIMPLE | people | ALL | sourceId | NULL | NULL | NULL | 101 | |
| 1 | SIMPLE | d1 | range | PRIMARY | PRIMARY | 2 | NULL | 30731 |
Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+

Still mysql is only using part of the key (I assume the d1.sourceId
part of the key), where I think it could use
both parts of the key, and reduce the number of rows down to 2, so that
mysql refers to both the constant and the
db.people.locationId constraints, as in the ver first query. I think a
possible problem is that mysql doesn't know
that each OR statement defines exactly one entry in the distance table,
but I'm just guessing.

Any ideas?

Thanks

Taras