Foodbank vouchers query results

queries.sql

/* Original query */
SELECT CONCAT(clientFirstName, clientLastName) AS fullName, count(*) AS ocurrences
FROM vouchers
WHERE vouchers.date BETWEEN "2011-01-01" AND "2011-12-31"
GROUP BY fullName
HAVING count(*) >= 2 AND count(*) <= 20
/* taking ~457ms */
/* for 2012: ~1.79s */
/* for 2013: ~5.24s */
/* for 2014: ~2.63s */

/* Query 1 */
SELECT ocurrences, count(*)
FROM (
  SELECT CONCAT(clientFirstName, clientLastName) AS fullName, count(*) AS ocurrences
  FROM vouchers
  WHERE vouchers.date BETWEEN "2011-01-01" AND "2011-12-31"
  GROUP BY fullName
  HAVING count(*) >= 2 AND count(*) <= 20
) AS client_ocurrences
GROUP BY ocurrences
/* Taking ~657ms */
/* for 2012: ~2.2s */
/* for 2013: ~6.3s */
/* for 2014: ~3.2s */

/* Enhancement to original query */
SELECT CONCAT(clientFirstName, clientLastName) AS fullName, count(*) AS ocurrences
FROM vouchers
WHERE vouchers.date BETWEEN "2011-01-01" AND "2011-12-31"
GROUP BY fullName
HAVING count(*) >= 2 AND count(*) <= 20
ORDER BY NULL
/* Taking ~420ms */
/* for 2012: ~1.4s */
/* for 2013: ~4.3s */
/* for 2014: ~2.3s */

EXPLAIN SELECT CONCAT(clientFirstName, clientLastName) AS fullName, count(*) AS ocurrences
FROM vouchers
WHERE vouchers.date BETWEEN "2014-01-01" AND "2014-12-31"
GROUP BY fullName
HAVING count(*) >= 2 AND count(*) <= 20
ORDER BY NULL
/* results:
+----+-------------+----------+-------+------------------------+------------------------+---------+------+--------+---------------------------------------------------+
| id | select_type | table    | type  | possible_keys          | key                    | key_len | ref  | rows   | Extra                                             |
+----+-------------+----------+-------+------------------------+------------------------+---------+------+--------+---------------------------------------------------+
|  1 | SIMPLE      | vouchers | range | index_vouchers_on_date | index_vouchers_on_date | 4       | NULL | 279364 | Using index condition; Using MRR; Using temporary |
+----+-------------+----------+-------+------------------------+------------------------+---------+------+--------+---------------------------------------------------+
*/