help - join query optimization

[Brian Kohan]

I am attempting to join two tables on the condition that the time (int) column in table 2 is in a range from the time column (int) in the rows of table 1.

The tables look like this:

Table1:
id - int
time - int
value1 - double
value2 - double
primary key: id, time
indexes: time

Table 2:
id - int
time - int
value1 - double
value2 - double
value3 - double
primary key: id, time
indexes: time

times are unix timestamps

The query looks like this:
SELECT * FROM (table1 JOIN table2 ON table2.time BETWEEN (table1.time - x) AND (table1.time + x); - where x is an arbitrary integer

This query does not make use of the time indexes and EXPLAIN indicates that time is a possible key but adding a FORCE INDEX (time) statement to the query does not make it use the index. JOIN type is ALL, my goal is to get it to be RANGE.

Curiously if you replace the between ranges with constant values (e.g. instead of table1.time - x) the query will use the time index and runs much faster. Also if you set the join condition to be table2.time = table1.time it will use the index and run much faster.

My guess is that the underlying implementation of JOIN does not allow for optimization using indexes when you are using a value from one of the tables to compute a range. If anyone can explain this to me or offer suggestions on improving this query's performance it would be very much appreciated.