MySQL index on column comparison

Question

How do you go about setting an index to be used for a column comparison like WHERE col1 > col2?

col1 and col2 are of DATETIME type.

Answer 1

Suppose the table's layout is as follows:

CREATE TABLE mytable
(
    id int not null auto_increment,
    col1 datetime,
    col2 datetime,
    primary key (id)
);

You may want to try one of two things

IDEA #1 : Create a column to store difference in seconds

Create the column and populate as follows

ALTER TABLE mytable ADD COLUMN dtdiff INT NOT NULL;
ALTER TABLE mytable ADD INDEX (dtdiff);
UPDATE mytable SET dtdiff = UNIX_TIMESTAMP(col1) - UNIX_TIMESTAMP(col2)l

If your table does not have foreign keys or constraints, do this instead:

CREATE TABLE mytable_new LIKE mytable;
ALTER TABLE mytable_new ADD COLUMN dtdiff INT NOT NULL;
ALTER TABLE mytable_new ADD INDEX (dtdiff);
INSERT INTO mytable_new
    SELECT id,col1,col2,
    UNIX_TIMESTAMP(col1) - UNIX_TIMESTAMP(col2)
    FROM mytable
;
ALTER TABLE mytable RENAME mytable_old;
ALTER TABLE mytable_new RENAME mytable;
DROP TABLE mytable_old;

Once done, your query would essentially

• SELECT * FROM mytable WHERE dtdiff > 0 for col1 > col2
• SELECT * FROM mytable WHERE dtdiff < 0 for col1 < col2

IDEA #2 : Create a separate table to store difference in seconds

CREATE TABLE mytable_dtdiff
(
    id int not null,
    dtdiff int not null,
    primary key (id),
    key dtdiff (dtdiff)
);
INSERT INTO mytable_dtdiff
SELECT id,UNIX_TIMESTAMP(col1) - UNIX_TIMESTAMP(col2)
FROM mytable;

Once done, your query would essentially

SELECT B.* FROM
(SELECT id FROM mytable_dtdiff WHERE dtdiff > 0) A
LEFT JOIN mytable B USING (id);

for col1 > col2 and

SELECT B.* FROM
(SELECT id FROM mytable_dtdiff WHERE dtdiff < 0) A
LEFT JOIN mytable B USING (id);

for col1 < col2

Give it a Try !!!

Answer 2

There's no straight-forward way of doing it in MySQL.

One workaround would be to store the value of comparison in another column, let's say cmp, put an index on it and use it instead of WHERE col1 > col2.

You might store -1 if col1 < col2, 0 if col1 = col2, or 1 if col1 > col2. This way you could easily get results for <, <=, =, >=, > and <> comparisons.

The easiest way to keep the cmp value in sync with values in col1 and col2 would be to update it in your application code whenever value of col1 or col2 is updated.

If your data is updated from more than one application or you don't want to put update logic in your app, you could use an UPDATE and INSERT trigger to calculate and update the value in cmp.