Opened 3 years ago

Closed 3 years ago

#33154 closed Cleanup/optimization (duplicate)

The Exact lookup generates suboptimal queries for BooleanField on MySQL

Reported by: Roman Miroshnychenko Owned by: Roman Miroshnychenko
Component: Database layer (models, ORM) Version: 3.2
Severity: Normal Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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.

Change History (3)

comment:1 by Roman Miroshnychenko, 3 years ago

Owner: changed from nobody to Roman Miroshnychenko
Status: new → assigned

comment:2 by Mariusz Felisiak, 3 years ago

Type: Uncategorized → Cleanup/optimization

Duplicate of #32691.

comment:3 by Mariusz Felisiak, 3 years ago

Resolution: → duplicate
Status: assigned → closed
Note: See TracTickets for help on using tickets.
Back to Top