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 !!!

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 !!!