index on datetime field

[jubripley]

Anyone know the correct way to index a date field in mysql?

I have a table (appointment) with a date field (appt_date yyyy-mm-dd). My script repeatedly calls a query that involves checking the date between a range.

 $query = "SELECT COUNT(*) FROM appointment a 
WHERE a.appt_date BETWEEN '$start_date' AND '$end_date' 
AND yadda yadda yadda"

(I also tried using <= and >= instead of BETWEEN, with the same results)

I have an index on appt_date, and I'm getting unusual behavior. It seems that mysql's ability to use the index depends on the date values I'm checking for.

For example, if $start_date is '2003-04-01' and $end_date is '2003-04-16', an EXPLAIN query shows that it uses the index in a 'range' type query with rows=1780.

Not too bad.

But if I do the query between '2003-01-01' and '2003-04-16', the EXPLAIN query says I'm doing an "ALL" type query (the worst kind) with NO index and rows=44457 (the size of the table)

Anyone had experience with this?

thanks

[dnszero]

I can't remember where I saw it, but basically there is a ceartin threshold where it's quicker to use the ALL method as it reduces the number of disk seeks and reads. So, I think that behaviour is somewhat expected.

[jubripley]

Yep. I think you're right.
Thanks.