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