[Django] #33154: The Exact lookup generates suboptimal queries for BooleanField on MySQL

[Django]

#33154: The Exact lookup generates suboptimal queries for BooleanField on MySQL
-------------------------------------+-------------------------------------
Reporter: Roman | Owner: nobody
Miroshnychenko |
Type: | Status: new
Uncategorized |
Component: Database | Version: 3.2
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
Currently `Exact` lookup uses "shortcut" condition syntax for
`BooleanField`, that is `WHERE foo` or `WHERE NOT foo` instead of explicit
syntax `WHERE foo = TRUE/FALSE` if `foo` is a BooleanField (`TINYINT(1)`
in MySQL).
The problem is that with "shortcut" syntax MySQL query planner ignores DB
indexes that include the field in question in contrast to explicit
condition syntax, resulting in performance hit for ORM-generated SQL
statements.

Proposed solution: Always use explicit comparison syntax in `Exact` lookup
for `BooleanField` with MySQL as a DB backend, that is `WHERE foo =
TRUE/FALSE` or even `WHERE foo = 1/0` since boolean is `TINYINT(1)` in
MySQL.

--
Ticket URL: <https://code.djangoproject.com/ticket/33154>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#33154: The Exact lookup generates suboptimal queries for BooleanField on MySQL
-------------------------------------+-------------------------------------

Reporter: Roman | Owner: Roman
Miroshnychenko | Miroshnychenko
Type: Uncategorized | Status: assigned
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Roman Miroshnychenko):

* owner: nobody => Roman Miroshnychenko
* status: new => assigned

--
Ticket URL: <https://code.djangoproject.com/ticket/33154#comment:1>

[Django]

#33154: The Exact lookup generates suboptimal queries for BooleanField on MySQL
-------------------------------------+-------------------------------------

Reporter: Roman | Owner: Roman
Miroshnychenko | Miroshnychenko
Type: | Status: assigned
Cleanup/optimization |

Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak):

* type: Uncategorized => Cleanup/optimization

Comment:

Duplicate of #32691.

--
Ticket URL: <https://code.djangoproject.com/ticket/33154#comment:2>

[Django]

#33154: The Exact lookup generates suboptimal queries for BooleanField on MySQL
-------------------------------------+-------------------------------------

Reporter: Roman | Owner: Roman
Miroshnychenko | Miroshnychenko

Type: | Status: closed

Cleanup/optimization |
Component: Database layer | Version: 3.2
(models, ORM) |

Severity: Normal | Resolution: duplicate
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak):

* status: assigned => closed
* resolution: => duplicate

--
Ticket URL: <https://code.djangoproject.com/ticket/33154#comment:3>