CREATE TABLE `tempdata` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `title` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tempdata_created_at` (`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=58511 DEFAULT CHARSET=utf8;


EXPLAIN EXTENDED SELECT MONTH(created_at) AS MONTH, COUNT(*) AS COUNT
FROM tempdata
WHERE YEAR(created_at)=2016
GROUP BY MONTH(created_at);

EXPLAIN EXTENDED SELECT
  SUM(CASE Month(created_at) WHEN 1 THEN 1 ELSE 0 END) AS 'January',
  SUM(CASE month(created_at) WHEN 2 THEN 1 ELSE 0 END) AS 'February',
  SUM(CASE month(created_at) WHEN 3 THEN 1 ELSE 0 END) AS 'March',
  SUM(CASE month(created_at) WHEN 4 THEN 1 ELSE 0 END) AS 'April',
  SUM(CASE month(created_at) WHEN 5 THEN 1 ELSE 0 END) AS 'May',
  SUM(CASE month(created_at) WHEN 6 THEN 1 ELSE 0 END) AS 'June',
  SUM(CASE month(created_at) WHEN 7 THEN 1 ELSE 0 END) AS 'July',
  SUM(CASE month(created_at) WHEN 8 THEN 1 ELSE 0 END) AS 'August',
  SUM(CASE month(created_at) WHEN 9 THEN 1 ELSE 0 END) AS 'September',
  SUM(CASE month(created_at) WHEN 10 THEN 1 ELSE 0 END) AS 'October',
  SUM(CASE month(created_at) WHEN 11 THEN 1 ELSE 0 END) AS 'November',
  SUM(CASE month(created_at) WHEN 12 THEN 1 ELSE 0 END) AS 'December',
  SUM(CASE year(created_at) WHEN 2016 THEN 1 ELSE 0 END) AS 'TOTAL'
FROM
    tempdata
WHERE
  created_at BETWEEN '2016/01/01' AND '2016/12/31'



1	SIMPLE	tempdata		index	tempdata_created_at	tempdata_created_at	5		57257	100.00	Using where; Using index; Using temporary; Using filesort

1	SIMPLE	tempdata		range	tempdata_created_at	tempdata_created_at	5		28628	100.00	Using where; Using index