Afraid I can't say as to whether you're missing anything, but I can say that MySQL/MariaDB support LIMIT/OFFSET it in the combined queries, so if this were implemented it would be nice to not add the SELECT * wrappers for the MySQL backend - there does seem to be a performance impact there, as the explain plan shows an extra derived table:
chainz@localhost [6]> explain (select c from t limit 1 offset 1) union all (select c from t2 limit 2); +------+-------------+-------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+-------+ | 1 | PRIMARY | t | ALL | NULL | NULL | NULL | NULL | 3 | | | 2 | UNION | t2 | ALL | NULL | NULL | NULL | NULL | 3 | | +------+-------------+-------+------+---------------+------+---------+------+------+-------+ 2 rows in set (0.00 sec) chainz@localhost [7]> explain (select * from (select c from t limit 1 offset 1) u0) union all (select c from t2 limit 2); +------+-------------+------------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------+------+---------------+------+---------+------+------+-------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | | | 2 | DERIVED | t | ALL | NULL | NULL | NULL | NULL | 3 | | | 3 | UNION | t2 | ALL | NULL | NULL | NULL | NULL | 3 | | +------+-------------+------------+------+---------------+------+---------+------+------+-------+ 3 rows in set (0.00 sec) On 8 December 2017 at 17:16, <[email protected]> wrote: > The relatively new QuerySet.union operator has quite a few limitations: > > [...] only LIMIT, OFFSET, COUNT(*), ORDER BY, and specifying columns (i.e. >> slicing, count(), order_by(), and values()/values_list()) are allowed on >> the resulting QuerySet. Further, databases place restrictions on what >> operations are allowed in the combined queries. For example, most databases >> don’t allow LIMIT or OFFSET in the combined queries. >> > > I think most of these could be circumvented by having the combined queries > wrapped as subqueries, e.g.: > > SELECT * FROM ( > SELECT f1, f2 FROM t1 ORDER BY f2 OFFSET 10 > ) > UNION > SELECT * FROM ( > SELECT f1, f2 FROM t2 ORDER BY f1 DESC LIMIT 3 > ) > > As far as I know, all relevant databases support this workaround and there > isn't any performance penalty to it. Am I missing something? > > — Thijs > > -- > You received this message because you are subscribed to the Google Groups > "Django developers (Contributions to Django itself)" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To post to this group, send email to [email protected]. > Visit this group at https://groups.google.com/group/django-developers. > To view this discussion on the web visit https://groups.google.com/d/ > msgid/django-developers/1e029596-3fd7-4796-988a- > 5aa51c522269%40googlegroups.com > <https://groups.google.com/d/msgid/django-developers/1e029596-3fd7-4796-988a-5aa51c522269%40googlegroups.com?utm_medium=email&utm_source=footer> > . > For more options, visit https://groups.google.com/d/optout. > -- Adam -- You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/django-developers. To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/CAMyDDM1CE%2B6cmrDCgxGFkDuBRyAchYb7orG9T6SVP54g9t%3DjyQ%40mail.gmail.com. For more options, visit https://groups.google.com/d/optout.
