SQL: efficiently select multiple rows groups from the same table

Consider the following table:

create table user_tasks
(
    id                      bigserial            primary key,
    type                    varchar(255)         not null,
    ...
);

I need to select 20 rows per page, but equally spread by type. Say, there are 10 distinct types, so I'm gonna need 2 rows of each. Every type has a different subset of filters that can be applied to it.

Here is how I do it right now, pseudo code:

array types = [...];

Collection typesSql = new Collection();
foreach (type of types) {
 typesSql.push('SELECT * FROM user_tasks WHERE type =: type AND ... LIMIT 10');
}

string sql = typesSql->implode(' UNION ALL ');

array result = DB::query(sql);

I select 10 rows of each type, because I don't know in advance if there are enough rows of this particular type in the database, then I reduce the overall number from 100 to 20 if needed.

The query is quite slow, probably because I union all the 10 different queries. Is it possible to do it in a more efficient way? For example, is it possible to write query that selects a new type only if there are less than < 20 rows selected from all the previous types?

Solution

Since Every type has a different subset of filters, you are destined to union several queries, like you showed in description.

Then, for the pagination to be efficient, you'll need to trace the page position for each type. You want to select 3 rows per type, to display 2 first rows and start the next page from the position of the 3rd row.

Let's display most recent records using id field to order records in descending order. If you need other criteria, you'll use corresponding unique index.

Starting page per type:

SELECT ...
FROM user_tasks
WHERE ... AND type = :typeN
ORDER BY id DESC
LIMIT 3

Normally this should use the id primary index to start scanning from the end of the table and quickly fetch the 3 corresponding rows, but may take another execution plan if filtered condition is also indexed and/or types are not evenly distributed. Use EXPLAIN to find out and address if needed.

Then you UNION ALL for all the types, like you showed above. Don't forget to parenthesize individual queries since you have ORDER BY / LIMIT clauses inside.

The final query for the 1st page looks like this:

(SELECT ... WHERE ... type = :type1 ...)
UNION ALL
(SELECT ... WHERE ... type = :type2 ...)
UNION ALL
...
UNION ALL
(SELECT ... WHERE ... type = :type10 ...)

Then, with 10 types and 3 rows per type you should have 30 rows returned. As mentioned, you display 2 rows of a type and keep the 3rd for the Show more link. This link will contain 10 ids to give position where to continue the next page per type.

If for specific type you have less then 3 rows, it means this type is at the end and you'll pass NULL as next position por this type. If you have 10 NULLs to pass, all rows are exhausted and no link should be displayed.

Next page per type:

SELECT ...
FROM user_tasks
WHERE ... AND type = :typeN AND id <= :idN
ORDER BY id DESC
LIMIT 3

Only AND id <= :idN part is added to each Nth type request.

This technique will be sufficient (and fastest) if only Show more link at the end of the list is acceptable.

If you need pagination with more classical (First-1-2-3-...-Last), adapt accordingly, but it will need to scan the whole table to build the Last link per type, so maybe a caching solution will be needed to store pages' references and avoid frequent scans.