5

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?

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
  • 1
    The OP is asking to speed it up; this won't help; it will still scan all dates. – Rick James Jan 9 '16 at 17:41
6

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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