mySQL Performant request to make a count request in a column of each each row of another request

Question

Hi, I have a table with devices, their buyed country and their buyed date. I want to retrieve : - the total number of devices by country - and the number of devices buyed since 1 month for each country.

I use this request to make this :

SELECT countryCode, COUNT(*) AS sinceBeginning, (
SELECT COUNT(*) 
FROM mytable 
WHERE countryCode = table1.countryCode
AND buyedDate >= DATE_SUB( CURDATE( ) , INTERVAL 1 MONTH )
) AS sinceOneMonth
FROM mytable AS table1
GROUP BY countryCode
ORDER BY countryCode ASC";

But the inside count for the column "sinceOneMonth" of each row is very expensive for performance. Is there a way to make this request better? Thanks.

Answer 1

Is there an index on (countrycode,buyedDate) ?

EDIT: With Steve Weet's query even further simplified:

SELECT 
  countrycode
, SUM(buyedDate >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)) AS this_month
, COUNT(*) AS all_time
FROM  mytable
GROUP BY countrycode

Answer 2

Is there an index on (countrycode,buyedDate) ? Yes on each one but not a multi column index (countrycode,buyedDate)

Answer 3

Your performance would probably improve with a composite index on countrycode and buyeddate and if you never directly query for a buyeddate without passing a countrycode as well then you could probably remove the index on countrycode and replace it with the composite.

The following query may perform better but not necessarily so, it depends as ever on the patterns of your data.

SELECT countrycode, 
       SUM(in_month) AS this_month, 
       COUNT(*)      AS all_time
FROM  ( 
         SELECT countrycode, 
                buyedDate >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH) AS in_month 
         FROM mytable
      ) AS summary 
GROUP BY countrycode;

Answer 4

Thank you Steve and Andomar,

With the request :

SELECT countrycode, COUNT( * ) AS sinceBeginning, SUM( buyedDate >= DATE_SUB( CURDATE( ) , INTERVAL 1 MONTH ) ) AS sinceOneMonth FROM mytable WHERE model =1 GROUP BY countrycode;

The performance is better : original request takes 4 seconds, now 2 seconds. Tests results : countrycode sinceBeginning sinceOneMonth 1 500101 20184 with 600.000 rows in the table