I use infinite scroll on my site and I have to return two parameters.
The first is totalCount, the second an array of users.
In users I show only 50 and then again 50 like Limit 50 OFFSET 10 so I cant use COUNT(*) because then I get only the limit count not the total count so I use a subquery. People can also search for last and surname. Now I am confused how my query should be.
here is my query:
SELECT
u.id,
u.firstname,
u.lastname,
c.company,
et.name as employement_type,
(
SELECT COUNT(id) as total FROM users WHERE tenant_id = $3 AND
(firstname LIKE '%'||$1||'%' OR lastname LIKE '%'||$1||'%')
)
AS total
FROM users u
LEFT JOIN employement_types et ON et.id = u.e_types_id
LEFT JOIN clients c ON u.company = c.id
WHERE u.tenant_id = $3
AND (u.firstname LIKE '%'||$1||'%' OR u.lastname LIKE '%'||$1||'%') LIMIT 50 OFFSET $2
How you would set an index based on this query ?
current my index is like that:
CREATE INDEX users_si_gin_index ON tbl USING gin (tenant_id, firstname gin_trgm_ops, lastname gin_trgm_ops);
Is this bad or wrong ? I am very thanful for your advice and help.
6
The LIKE and OR predicates will prevent indexes being used properly anyway. And OFFSET FETCH gets slower and slower the higher the offset. Getting the total page count is
If you are doing infinite scrolling this is the perfect use case for Keyset Pagination (paging by key, rather than by row number).
Note the following:
- You need to order by a unique key. You can use
users.idfor this. Your existing query is anyway broken because it has noORDER BYand therefore the row ordering may be different every time. - Don’t try to get the total count. It’s very costly calculate, and the user doesn’t usually need to know it. If you really do need it then do a separate query for it, and do it only once.
So for the first run:
SELECT
u.id,
u.firstname,
u.lastname,
c.company,
et.name as employement_type
FROM users u
LEFT JOIN employement_types et ON et.id = u.e_types_id
LEFT JOIN clients c ON u.company = c.id
WHERE u.tenant_id = $3
AND (u.firstname LIKE '%'||$1||'%' OR u.lastname LIKE '%'||$1||'%')
ORDER BY u.id
LIMIT 50
On the subsequent runs, add a WHERE predicate
AND u.id > $4
and $4 should be the highest ID you got on the previous run.
3