Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 1 down vote accepted
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
share|improve this answer
1  
The OP is asking to speed it up; this won't help; it will still scan all dates. – Rick James Jan 9 at 17:41

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.