Opened 18 years ago
Last modified 17 years ago
#2306 closed defect
Many-to-many queries very slow on MySQL 3.23.58 — at Initial Version
| Reported by: | anonymous | Owned by: | Adrian Holovaty |
|---|---|---|---|
| Component: | Database layer (models, ORM) | Version: | dev |
| Severity: | normal | Keywords: | qs-rf-fixed |
| Cc: | mir@…, freakboy3742@… | Triage Stage: | Accepted |
| Has patch: | no | Needs documentation: | no |
| Needs tests: | no | Patch needs improvement: | no |
| Easy pickings: | no | UI/UX: | no |
Description
We have a MySQL 3.23.58 database with about 1000 Room and Photo objects, connected by a many-to-many relationship.
Given a room r, asking Django to evaluate r.photos.all() runs an explicit join:
SELECT kords3_photo.id,kords3_photo.image FROM kords3_photo LEFT OUTER JOIN kords3_room_photos AS m2m_kords3_photo__room ON kords3_photo.id = m2m_kords3_photo__room.photo_id WHERE (m2m_kords3_photo__room.room_id = 1) ORDER BY kords3_photo.image ASC
which takes around 1 second, when the same query rewritten as an implicit join:
SELECT kords3_photo.id,kords3_photo.image FROM kords3_photo , kords3_room_photos WHERE kords3_photo.id = kords3_room_photos.photo_id AND kords3_room_photos.room_id = 1 ORDER BY kords3_photo.image ASC
would take under a millisecond.