2

I have to get a records/count on monthly basis. There is no of ways to do ---

1

SELECT COUNT(1)AS approved FROM lu_registration 
WHERE MONTH(approved_date)=MONTH(NOW()) AND YEAR(approved_date)=YEAR(NOW());

2 

SELECT COUNT(1)AS approved FROM lu_registration 
WHERE DATE_FORMATE(approved_date, '%Y-%m')=DATE_FORMATE(NOW(), '%Y-%m');

3

SELECT COUNT(CASE WHEN MONTH(approved_date)=MONTH(NOW()) 
AND YEAR(approved_date)=YEAR(NOW()) THEN 1 END)AS approved FROM lu_registration;

4

SELECT COUNT(CASE WHEN DATE_FORMATE(approved_date, '%Y-%m')=DATE_FORMATE(NOW(), '%Y-%m')
 THEN 1 END)AS approved FROM lu_registration;

Which one is best.

Actual Senerio: There is a month filter by which we select month,and get as 04-2016. So that we can use date_format (%m-%y) or month=04 & year=2016 to match for getting records.but which one is performance wise better

Actual query to optimize is:

SELECT COUNT(CASE WHEN status='A' AND MONTH(approved_date)=04
AND YEAR(approved_date)=2016 THEN 1 END)AS approved, COUNT(CASE WHEN status='D' AND MONTH(reject_date)=04 
AND YEAR(reject_date)=2016 THEN 1 END)AS rejected FROM lu_registration;

Here is approved_date & reject_date two different column or approved user also be rejected

Zigri2612
  • 2,279
  • 21
  • 33

2 Answers2

3

You are trying to count records in a date range. With respect, none of your suggested queries are efficient. None can exploit a MySQL index on your approved_date column. None of them is sargeable. That's going to harm performance, especially as your application adds records with dates going back years.

Instead, you need a query of the form

 SELECT COUNT(*) AS approved
   FROM table
  WHERE approved_date >= <<<00:00 on first date>>>
    AND approved_date <  <<<00:00 on day after last date>>>

This can do an index scan on an index on your approved_date column.

So, now the trick needed is obtaining correct values for <<<00:00 on first date>> and <<<00:00 on day after last date>>> from NOW(). Here's what you do.

  1. LAST_DAY(NOW()) gives 00:00 on the last day of the present month.
  2. LAST_DAY(NOW()) + INTERVAL 1 DAY gives 00:00 on the first day of next month.
  3. LAST_DAY(NOW()) + INTERVAL 1 DAY - INTERVAL 1 MONTH gives 00:00 on the first day of the present month.

So, your query becomes

 SELECT COUNT(*) AS approved
   FROM lu_registration
  WHERE approved_date >= LAST_DAY(NOW()) + INTERVAL 1 DAY
    AND approved_date <  LAST_DAY(NOW()) + INTERVAL 1 DAY - INTERVAL 1 MONTH

It chooses the correct date range, and, it runs fast.

(Notice I used COUNT(*) in place of your COUNT(1). That is because COUNT(*) is the common way of counting records. Sometimes query planner modules in RDMS software optimize it specially.)

Community
  • 1
  • 1
O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • Great answer, great explanation, plus I learned a new word! Thank you! :-) – PerlDuck Apr 15 '16 at 11:15
  • There is a month filter by which we select month,and retrieve as 04-2016. So that we can use date_format (%m-%y) or month=04 & year=2016 to get records.but which one is performance wise better – Zigri2612 Apr 15 '16 at 17:34
  • @Zigri2612 - A range (that can use an index) is always better than any kind of expression. – Rick James Apr 15 '16 at 20:44
  • Any `WHERE` expression involving `f(column)` will perform pretty much the same as any other. They all will perform very badly indeed because they are not sargeable. To satisfy such a query the MySQL server software must examine every row in the database. That's called a full table scan or maybe a full index scan. Slow. – O. Jones Apr 16 '16 at 01:08
  • SELECT COUNT(CASE WHEN status='A' AND MONTH(approved_date)=04 AND YEAR(approved_date)=2016 THEN 1 END)AS approved, COUNT(CASE WHEN status='D' AND MONTH(reject_date)=04 AND YEAR(reject_date)=2016 THEN 1 END)AS rejected FROM lu_registration; – Zigri2612 Apr 16 '16 at 02:31
1

I think first one is Better--

SELECT COUNT(1)AS approved FROM lu_registration 
WHERE MONTH(approved_date)=MONTH(NOW()) AND YEAR(approved_date)=YEAR(NOW());
Zigri2612
  • 2,279
  • 21
  • 33