Fastest way to select by year-month?

Question

Per this comment there's at least 8 different ways to select records by year and month:

1. where date like '2005-01-%'
2. where DATE_FORMAT(date,'%Y-%m')='2005-01'
3. where EXTRACT(YEAR_MONTH FROM date)='200501'
4. where YEAR(date)='2005' and MONTH(date)='1'
5. where substring(date,1,7)='2005-01'
6. where date between '2005-01-01' and '2005-01-31'
7. where date >= '2005-01-01' and date <= '2005-01-31'
8. where date IN('2005-01-01', '2005-01-02', '2005-01-03', '2005-01-04', '2005-01-05', '2005-01-06', '2005-01-07', '2005-01-08', '2005-01-09', '2005-01-10', '2005-01-11', '2005-01-12', '2005-01-13', '2005-01-14', '2005-01-15', '2005-01-16', '2005-01-17', '2005-01-18', '2005-01-19', '2005-01-20', '2005-01-21', '2005-01-22', '2005-01-23', '2005-01-24', '2005-01-25', '2005-01-26', '2005-01-27', '2005-01-28', '2005-01-29', '2005-01-30', '2005-01-31')

Which is the fastest in the WHERE clause? Which is fastest in a GROUP BY? Can any of them take advantage of an index?

My particular query looks like this:

select count(*) 
from quotes quote
where EXTRACT(YEAR_MONTH FROM qtime) = :date

Where qtime is a TIMESTAMP.

But I also need to get the month with the most quotes:

select date_format(qtime,'%Y-%m') d,count(*) c
from quotes
group by EXTRACT(YEAR_MONTH FROM qtime)
order by c desc, qid asc limit 1;

in which case I can't use the comparison operators.

Answer 1

I analysis all these queries and got the result.

between => Showing rows 0 - 29 (158 total, Query took 0.0030 sec)

like => Showing rows 0 - 29 (167 total, Query took 0.0031 sec)

DATE_FORMAT => Showing rows 0 - 29 (167 total, Query took 0.0237 sec)

EXTRACT => Showing rows 0 - 29 (167 total, Query took 0.0031 sec)

YEAR, MONTH => Showing rows 0 - 29 (167 total, Query took 0.0187 sec)

substr => Showing rows 0 - 29 (167 total, Query took 0.0224 sec)

> < => Showing rows 0 - 29 (158 total, Query took 0.0030 sec)

So i can say the use of between is best and faster from any other. Though use of greater than and less than is the same result but i think the between is pretty smart.