2

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

rfusca
  • 7,435
  • 2
  • 30
  • 34
  • What's wrong with storing the difference? That would be my solution. – Paul Tomblin Mar 31 '09 at 17:48
  • Because I only mentioned 2 dates, but the table actually has about 50, each of which might need to be compared to another. Storing the difference of every possible date just isn't going to fly. – rfusca Apr 02 '09 at 15:36

2 Answers2

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.

feihtthief
  • 6,403
  • 6
  • 30
  • 29
  • There is a time difference - shaves about 10-20 seconds off. I have several dates to compare against each other so I hate to have to store the difference and index that, but I think that's what I'll just have to do. The DB writes once and reads all day - read speed is critical. – rfusca Mar 31 '09 at 18:03
0

i'm trying to find the answer my self.

but I 've a clue : index on function.

I've used it on Oracle Dbs.

but seams MySQL lacks the support :

Is it possible to have function-based index in MySQL?

Community
  • 1
  • 1
quazardous
  • 846
  • 10
  • 15