How to make a Derived table?

Using Django Using the ORM
1

We are developing a feature involving selecting from the main table and assembling with the dynamic attributes by left outer join. For example, see the below snippet for filtering the main table transaction, and we want to filter on selecting so that the rest operations only apply to a smaller data set.

transaction = Transaction.active_objects.filter(
    product_id=product_id
).order_by('-id').all()[27000:27020]

However, Django places the WHERE clause at the rear of the SQL query. As a result, the MariaDB v10.5 database performs all the left outer join operators on the comprehensive data set before it applies the filtering condition. Therefore, our test’s running time was nearly 20 seconds.

We made a hand-written improvement to the SQL query and reduced the running time to instant.

The change was to make a “Derived” table. This way, we forced the query to narrow to one page worth of data before assembling the expensive left outer join parts. See the SQL snippet below for selecting the Derived table:

...
from
(
  select * from 
  `app_transaction`  
  where
    (not `app_transaction`.`rec_del`
      and `app_transaction`.`product_id` = __PRODUCT_ID__)
  order by
    `app_transaction`.`id` desc
  limit 20 OFFSET 27000
) as app_transaction
...

Our Question:

How to implement the hand-written improvement within the Django framework, preferably not raw query? We highly appreciate any hints and suggestions.

Technical Details:

  • The original SQL query generated by Django ORM, taking nearly 20 seconds on our test setting (including the computer and size of the data set):
select
  `app_transaction`.`id`,
  `app_transaction`.`product_id`,
  -- ... a few more native columns of the `transaction` table
  eav_firstname.`value_text` as `eav_firstname`,
  -- ... 20+ more EAV attributes assembled by the left outer join operations
from
  `app_transaction`
left outer join `eav_value` eav_firstname on
  (`app_transaction`.`id` = eav_firstname.`entity_id`
    and (eav_firstname.`entity_ct_id` = __CONTENTTYPE_ID__)
      and (eav_firstname.`attribute_id` = __ATTRIBUTE_ID_OF_FIRSTNAME__))
-- ... 20+ more left outer join operations to assemble the EAV attributes
where
  (not `app_transaction`.`rec_del`
    and `app_transaction`.`product_id` = __PRODUCT_ID__)
order by
  `app_transaction`.`id` desc
limit 20 OFFSET 27000
;
  • The hand-improved SQL query, taking less than one-second running time for the same setting as above:
select
  `app_transaction`.`id`,
  `app_transaction`.`product_id`,
  -- ... a few more native columns of the `transaction` table
  eav_firstname.`value_text` as `eav_firstname`,
  -- ... 20+ more EAV attributes assembled by the left outer join operations
from
(
  select * from 
  `app_transaction`  
  where
    (not `app_transaction`.`rec_del`
      and `app_transaction`.`product_id` = __PRODUCT_ID__)
  order by
    `app_transaction`.`id` desc
  limit 20 OFFSET 27000
) as app_transaction
left outer join `eav_value` eav_firstname on
  (`app_transaction`.`id` = eav_firstname.`entity_id`
    and (eav_firstname.`entity_ct_id` = __CONTENTTYPE_ID__)
      and (eav_firstname.`attribute_id` = __ATTRIBUTE_ID_OF_FIRSTNAME__))
-- ... 20+ more left outer join operations to assemble the EAV attributes
;