MySQL Forums
Forum List  »  Optimizer & Parser

help - join query optimization
Posted by: Brian Kohan
Date: April 05, 2007 09:18AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
help - join query optimization
2710
April 05, 2007 09:18AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.