MySQL Executing a query to make a query to count in a column of each row of another query
I have a table with devices, their country bought out and the date they were purchased. I want to get: - the total number of devices by country - and the number of devices purchased from 1 month for each country.
I am using this query to do 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 internal number for the "sinceOneMonth" column of each row is very expensive for performance. Is there a way to make this query better? Thanks.
a source to share
Your performance is likely to improve with a composite index by countrycode and buyeddate, and if you never directly query buyeddate without passing in the country code, then you can probably remove the index by countrycode and replace it with a composite one.
The following query might work better, but it doesn't have to be, it always depends on your data patterns.
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;
a source to share
Thanks Steve and Andomar,
With a request:
Country code SELECT, COUNT (*) AS, starting with Buginning, SUM (buyedDate> = DATE_SUB (CURDATE (), INTERVAL 1 MONTH)) AS sinceOneMonth From WHERE table model = 1 Country code by country;
Better performance: original query takes 4 seconds, now 2 seconds. Test results: country code since the beginning since the release of 1,500101 20184 with 600,000 rows in the table
a source to share