Optimization of a statistical GroupBy statement

When checking slow SQL statements, it is found that execution of a statistical SQL statement is too slow, as follows: the original SQLSELECTplatform, channel, COUNT (DISTINCT (platformUserId) ascntFROM (SELECTplatform, channel, platformUser

When checking slow SQL statements, it is found that execution of a statistical SQL statement is too slow, as follows: the original SQLSELECTplatform, channel, COUNT (DISTINCT (platformUserId) ascntFROM (SELECTplatform, channel, platformUser

When checking slow SQL statements, it is found that execution of a statistical SQL statement is too slow, as shown below:

Original SQL

SELECT platform, channel, COUNT (DISTINCT (platformUserId) as cnt FROM (

SELECT platform, channel, platformUserId, MIN (insertTimestamp) as rtime

FROM tsz_user

Group by platform, channel, platformUserId

) A where a. rtime> = 1392393600 and a. rtime <1392480000

Group by platform, channel;

Execution time:

2 minutes 33 seconds

SQL statement after optimization

SELECT platform, channel, COUNT (DISTINCT (platformUserId) as cnt FROM (

SELECT platform, channel, platformUserId, MIN (insertTimestamp) as rtime

FROM tsz_user

Group by platform, channel, platformUserId order by null

) A where a. rtime> = 1392393600 and a. rtime <1392480000

Group by platform, channel order by null;

Execution time:

Time: 55.22 seconds

Execution Plan:

Conclusion:

BY default, MySQL sorts all group by col1, col2. .. fields. If the query includes group by, you can specify ORDER By NULL to disable sorting to avoid consumption of sorting results.

Reference manual:

This article is from the "hechun's technical column" blog. Be sure to keep this source