Querying Date Range from DB

PHP_Novice2

I have a database with approximately 5 million records, and I'm trying to find the earliest and most recent values of a DATETIME field. Right now, I run this query:

SELECT MIN(MyDate) AS MinDate, MAX(MyDate) AS MaxDate FROM MyTable;

But it takes a good 5-6 seconds, probably because of the database size. Is there a more efficient way to handle this?

bradgrafelman

What if you kept a separate table, say statistics, that had two columns; stat_name and stat_value. The two rows of interest for you would then be those where stat_name='MaxDate' or stat_name='MinDate'

Every time you INSERT a record into this table (sounds like a good candidate for a trigger), you would have the DB check if the date your inserting is less than the MinDate row above or greater than the MaxDate row. If so, update the new statistics table with the date you just INSERT'ed.

I'm not guaranteeing that this is the best way to go about it; a DB guru will have to come along and critique me on this method. :p

walterquez

bradgrafelman;10897574 wrote:
Every time you INSERT a record into this table (sounds like a good candidate for a trigger)

Not a bad idea, except that you would also have to take into account for any deletes and updates.

You can have the trigger run a query for the max and min date and put update the values in the stats table.

How often is this query used? You could use an index, but this depends on other factors.

Sxooter

Is MyDate indexed?