Optimise SQL query joined on DATE(datetime) = date?

Question

I have two tables: one has a datetime column, the other has a date column.

I want to join the two tables like this:

SELECT
  t1.dt,
  t2.d
FROM
  table1 t1,
JOIN
  table2 t2
ON
  DATE(t1.dt) = t2.date

But obviously, this kills the index on t1.dt.

What's the best strategy to get around this? The simplest approach would be to add a new column to t1 (and set it equal to date(t1.dt)), but I don't like the idea of duplicating data.

Is there a neat way to get around this?

Answer 1

Your datetime column consists of a date and a time part. As you would like to index the date part individually, it would make sense to split the datetime column into two individual column, one for the time part and the other one for the date part. The date column can then be indexed and the index can be used in your query. With this approach you do not duplicate any data at all.

Answer 2

I'm not exactly sure, but this might force MySQL to iterate all rows as you substract a date from the datetime. You should use EXPLAIN to be sure. You can get around this by doing something like this:

(t1.dt >= "2010-06-24 00:00:00"
  AND t1.dt < ("2010-06-24 00:00:00" + INTERVAL 1 DAY) )

Edit: Just tried using Date(), and it seems MySQL still uses indexes:

CREATE TEMPORARY TABLE t1 (
    dt DATETIME,
    INDEX iDt (dt)
);

INSERT INTO t1 VALUES
    (NOW()),
    (NOW() - INTERVAL 1 DAY),
    (NOW() - INTERVAL 1 DAY),
    (NOW() - INTERVAL 2 DAY);

CREATE TEMPORARY TABLE t2 (
    dt DATETIME,
    INDEX iDt (dt)
);

INSERT INTO t2 VALUES
    (NOW()),
    (NOW() - INTERVAL 1 DAY),
    (NOW() - INTERVAL 1 DAY),
    (NOW() - INTERVAL 2 DAY);

# t1: Using index. t2: Using where+index
EXPLAIN EXTENDED
SELECT
    *
FROM t1
    JOIN t2 ON DATE(t1.dt) = DATE(t2.dt)

Answer 3

Try this. This will make use of index

SELECT 
  t1.dt, 
  t2.d 
FROM 
  table1 t1, 
JOIN 
  table2 t2 
ON 
  t1.dt>=t2.date  and
  t1.dt <(t2.date + INTERVAL 1 DAY)