Optimise SQL query joined on DATE(datetime) = date?
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 app开发者_如何学Goroach 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?
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.
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)
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)
精彩评论