#32691: Django 3.2 boolean field SQL output is causing a performance regression
in
MySQL.
-------------------------------------+-------------------------------------
Reporter: Todor Velichkov | Owner: nobody
Type: | Status: closed
Cleanup/optimization |
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: bool, booleanfield, | Triage Stage:
orm, sql, mysql | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Todor Velichkov):
The issue is not that the index will not be created, the index will be
created, but it will not be used against expressions like:
{{{
WHERE `mytable`.`myfield`
}}}
because such an expression evaluates to `True` for a range of values (in
the case of signed `tinyint(1)` the range is `-128..127` except `0`) which
turns the query into a `range` query which are
[https://dev.mysql.com/doc/refman/8.0/en/range-optimization.html hard to
optimize]. MySQL needs a constant value like:
{{{
WHERE `mytable`.`myfield` = 1
}}}
in order for the index to be used.
An example: If we have a row with a value of `myfield = 2` (MySQL allows
this because the column is `tinyint(1)`) then:
- The first expression will include this row in the results because `2`
evaluates to `True`
- The second expression will not include this row because `2 != 1`
Django only stores `0` and `1` why its expression is including rows which
Django itself do not store.
--
Ticket URL: <https://code.djangoproject.com/ticket/32691#comment:7>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/072.04f96759dcc0a6440225a5c7e9c15264%40djangoproject.com.
