Count distinct records in a JOINed SQL query

Question

I have two tables with a 1-many relationship, Device and TestResult.

My application has a filter builder front-end that allows a user to retrieve TestResults based on whatever filters they specify. This generates queries similar to this:

SELECT d.id,
       d.serialNumber,
       d.createdAt,
       r.id,
       r.testBlockId,
       r.deviceId,
       r.type,
       r.fieldResponses,
       r.stationName,
       r.summary,
       r.createdAt
FROM Device AS d
INNER JOIN TestResult AS r ON r.deviceId = d.id
WHERE d.id IN
    (SELECT id
     FROM Device
     WHERE serialNumber BETWEEN '000000001000' AND '000000002020'
     ORDER BY serialNumber ASC
     LIMIT 570, 10)
  AND r.createdAt BETWEEN '2020-03-27T11:54:43.100Z' AND '2020-04-01 09:21:02.362768'
ORDER BY serialNumber ASC;

This query limits the number of Devices returned, which handles paging on the front-end. I'm also generating a query that counts the total number of Devices that match the filters, without the OFFSET and LIMIT values. This is used to tell the front-end how many pages of records there are.

If I simply run the above query but change the SELECT statement to SELECT COUNT(DISTINCT d.id) as totalDevices and remove the LIMIT, it's very slow. So instead I'm generating this query to find the count:

SELECT COUNT(DISTINCT d.id) AS totalDevices
FROM Device AS d
INNER JOIN TestResult AS r ON r.deviceId = d.id
WHERE serialNumber BETWEEN '000000001000' AND '000000002020'
  AND r.createdAt BETWEEN '2020-03-27T11:54:43.100Z' AND '2020-04-01 09:21:02.362768';

This query runs faster, but it still isn't fast. I'm wondering if there is a better way to retrieve a count of the distinct Device records that would be returned from this query.

This query shows the following execution time with 1,000,000 records:

Run Time: real 8.090 user 1.562500 sys 6.500000

Answer 1

I've modified the query to use a GROUP BY instead of a COUNT DISTINCT, which has sped up the execution time.

SELECT COUNT(*) AS totalDevices
FROM (
  SELECT d.id
  FROM Device AS d
  INNER JOIN TestResult AS r ON r.deviceId = d.id
  WHERE serialNumber BETWEEN '000000001000' AND '000000002020'
    AND r.createdAt BETWEEN '2020-03-27T11:54:43.100Z' AND '2020-04-01 09:21:02.362768'
  GROUP BY d.id
);

This query shows the following execution time with 1,000,000 records:

Run Time: real 3.433 user 0.953125 sys 2.484375

The COUNT DISTINCT query shows the following execution time:

Run Time: real 8.090 user 1.562500 sys 6.500000

So the GROUP BY query takes just under half of the time of the COUNT DISTINCT query.