MySQL group by like statement?

Question

I need to get some stats out of our MySQL-DB and wonder if there is a faster way in doing this.

Basically I need info about user registration for any given month before including seperation by attributes.

e.G:

SELECT count(*) AS c  
FROM `users` 
WHERE `date_created` LIKE '2015-10%' 
  AND `country` = 'DE'

This would return a number for October 2015. How could I alter this statement to return results for all months within one statement?

I would also be interested in adding something like AND confirmed = 1 as separate result.

Is this possible with just one MySQL-Statement?

Answer 1

SELECT
   EXTRACT(YEAR FROM date_created) AS yr,   -- for each year
   EXTRACT(MONTH FROM date_created) AS mth, -- & month combination 
   count(*) AS c,                                 -- count all rows 
   SUM(CASE WHEN confirmed = 1 THEN 1 ELSE 0 END) -- count only confirmed rows 
FROM users
WHERE country = 'DE'
GROUP BY yr, mth

Answer 2

Two things are needed to make it run faster:

Get suitable index

Without confirmed = 1, you need

INDEX(country, date_created)

With it, you need

INDEX(country, confirmed, date_created)

Avoid hiding the date in a function

LIKE '2015-10%' cannot use an index on date_created, nor can MONTH(date_created), so let's reformulate the query:

WHERE date_created >= '2015-10-01'
  AND date_created  < '2015-10-01' + INTERVAL 1 MONTH
  AND confirmed = 1   -- add this if you want to limit to confirmed rows

(This assumes that date_created is a DATETIME or TIMESTAMP datatype.)

All Months

This query can still use the two indexes I suggest, however, it won't necessarily use the date_created part.

SELECT LEFT(date_created, 7) AS YYYY_MM,
       COUNT(*) AS ct
    FROM tbl
    WHERE country = 'DE'
      AND confirmed = 1   -- if you want this
    GROUP BY YYYY_MM;

Note about country_code

A common mistake in defining the schema (please provide SHOW CREATE TABLE) is to define it as CHAR(2) and default to utf8. This leads to using 6 bytes, when 2 would suffice. Change to CHAR(2) CHARACTER SET ascii.