patternsqlMinor

MySQL index on column comparison

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

indexmysqlcomparisoncolumn

Problem

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.

Solution

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




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

Code Snippets

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

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

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;

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;

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

Context

StackExchange Database Administrators Q#30727, answer score: 3

Revisions (0)

No revisions yet.