Geek Answers Handbook

The fastest way to choose a year-month?

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

  • where date like '2005-01 -%'
  • where DATE_FORMAT (date, '% Y-% m') = '2005-01'
  • where EXTRACT (YEAR_MONTH FROM date) = '200501'
  • where YEAR (date) = '2005' and MONTH (date) = '1'
  • where substring (date, 1,7) = '2005-01'
  • where the date is between '2005-01-01' and '2005-01-31'
  • where date> = '2005-01-01' and date <= '2005-01-31'
  • 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 ')

What is the fastest WHERE clause ? What is faster in GROUP BY ? Can any of them use the index?

My specific request looks like this:

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

Where qtime is 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;

and in this case I cannot use comparison operators.

+7
mysql
source share
1 answer

I analyze all these queries and get 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)

Therefore, I can say that use is between a better and faster pace from any other. Although the use of larger and smaller than the same result, but I think between them is pretty smart.

+5
source share

More articles:


All Articles