Queryset filter Q order changed

[Edward Villegas-Pulgarin]

Using filters in order explicitly expressed by user is important because take advantage of indexes.

When apply this query

TaxInput.objects.all(
                force_visibility=True
            ).filter(
                Q(filling_id=pk, updated_at__gt=last_engine.created_at)
                | Q(filling_id=pk, deleted__gt=last_engine.created_at),
            )

the MySQL generated query is

SELECT
  *
FROM
  `taxobjects_taxinput`
WHERE
  ( `taxobjects_taxinput` . `filling_id` = ?
    AND `taxobjects_taxinput` . `updated_at` > ? )
  OR ( `taxobjects_taxinput` . `deleted` > ?
    AND `taxobjects_taxinput` . `filling_id` = ? )
LIMIT
  ?

But should be

SELECT
  *
FROM
  `taxobjects_taxinput`
WHERE
  ( `taxobjects_taxinput` . `filling_id` = ?
    AND `taxobjects_taxinput` . `updated_at` > ? )
  OR ( `taxobjects_taxinput` . `filling_id` > ?
    AND `taxobjects_taxinput` . `deleted` = ? )
LIMIT
  ?

This change of order create a 10x factor in query time, because this table has index filing_id, and also index (filing_id, deleted).

Using Django 4.2.1 and SafeDelete 1.3.1 (because this is used force_visibility=True in all manager).

[David Sanders]

Hi,

Thanks for the report though I don't see an issue with Django here. There are many reasons why a database will choose to ignore indexes but sadly parameter ordering is not one of those. (To test this I recreated your setup and MySQL reported that the indexes are a possibility both ways.)

I'd encourage you to seek help from one of the Django support channels where there are many friendly people who are eager to help: ​https://www.djangoproject.com/community/

You'll likely need to paste detailed descriptions of your models, query, setup & any query plans (you may even be asked to paste memory settings etc as this will have an impact on query planning).

If it is discovered there is an issue with Django feel free to reopen this with links to discussion & findings.

Good luck!

[Simon Charette]

If that can be of any help, we've observed that MySQL's query planer can be pretty bad at using composite indexes when OR conditions are involved and had to resort to using UNION to target them.

Re-writing your lookup to move the filling_id=pk outside of the OR, since it's used by both, might help MySQL figuring out that the filing_id index can be used at least.

TaxInput.objects.filter(
    filling_id=pk,
    Q(updated_at__gt=last_engine.created_at) | Q(deleted__gt=last_engine.created_at)
)

SELECT
  *
FROM
  `taxobjects_taxinput`
WHERE
    `taxobjects_taxinput` . `filling_id` = ?
    AND (
        `taxobjects_taxinput` . `updated_at` > ?
        OR `taxobjects_taxinput` . `deleted` > ?
    )

Otherwise I'm pretty sure that using union will perform great but it comes with some ORM limitations #28519.

TaxInput.objects.filter(
    filling_id=pk,
    updated_at__gt=last_engine.created_at,
).union(
    TaxInput.objects.filter(
        filling_id=pk,
        deleted__gt=last_engine.created_at,
    )
)

SELECT
  *
FROM
  `taxobjects_taxinput`
WHERE
    `taxobjects_taxinput` . `filling_id` = ?
    AND `taxobjects_taxinput` . `updated_at` > ?
UNION
SELECT
  *
FROM
  `taxobjects_taxinput`
WHERE
    `taxobjects_taxinput` . `filling_id` = ?
    AND `taxobjects_taxinput` . `deletedt` > ?