comparing two fields in mysql -indexes?

Question

I'm fairly certain I know the answer to this, but any ideas would be extremely helpful...

First of all, this is MySQL 5.1.32-community. I have a large table (millions of rows, can't reduce the size - that is the reduced size) and the pertinent fields are two datetime fields. We'll call it date1 and date2. I need to run a select query that returns all rows (a actually UUID varchar(40) where date1 > date2.

SELECT id FROM table1 WHERE date1 > date2;

It takes too long to run (2 minutes'ish). Indexes on the dates themselves obviously do nothing. Short of storing the difference between the two dates and indexing that - any methods to decrease the query time? This seems like such a basic question, but I always wonder if maybe I'm just missing something...

Answer 1

Have you run a count to see if there just is really so much data being returned that it will take that long to return it all?

select count(ID) from table1 WHERE date1 > date2;

The query seems simple enough and your data volume might just be the problem.