MySQL index on column comparison How do you go about setting an index to be used for a column comparison like WHERE col1 &gt; col2?

col1 and col2 are of DATETIME type.
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 &gt; 0 for col1 &gt; col2
SELECT * FROM mytable WHERE dtdiff &lt; 0 for col1 &lt; 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 &gt; 0) A
LEFT JOIN mytable B USING (id);

for col1 &gt; col2 and
SELECT B.* FROM
(SELECT id FROM mytable_dtdiff WHERE dtdiff &lt; 0) A
LEFT JOIN mytable B USING (id);

for col1 &lt; col2
Give it a Try !!!