Django

Code

Ticket #2306 (closed: fixed)

Opened 2 years ago

Last modified 3 weeks ago

Many-to-many queries very slow on MySQL 3.23.58

Reported by: anonymous Assigned to: nobody
Component: Database wrapper Version: SVN
Keywords: qs-rf-fixed Cc: mir@noris.de, freakboy3742@gmail.com
Triage Stage: Accepted Has patch: 0
Needs documentation: 0 Needs tests: 0
Patch needs improvement: 0

Description (Last modified by adrian)

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.

Attachments

2306.diff (444 bytes) - added by Marc Fargas <telenieko@telenieko.com> on 05/19/07 21:25:49.
Just add a note on database backend notes.

Change History

07/07/06 23:11:44 changed by adrian

OK, why the heck is Django doing an outer join? This is upsetting. Thanks for pointing this out.

07/08/06 05:55:22 changed by lukeplant

I changed it in r2613 due to #1535

I was unaware of serious performance implications - sorry!

07/10/06 06:07:30 changed by mir@noris.de

  • cc set to mir@noris.de.

The ORM does have severe problems handling 'or' and 'and' for queries with joins. #1535 only solved parts of them. Look at #1801 and #2080.

07/10/06 21:36:35 changed by adrian

  • status changed from new to assigned.
  • description changed.

07/14/06 02:25:54 changed by russellm

  • cc changed from mir@noris.de to mir@noris.de, freakboy3742@gmail.com.

02/22/07 00:51:58 changed by Simon G. <dev@simon.net.nz>

  • stage changed from Unreviewed to Accepted.

02/28/07 19:39:25 changed by mtredinnick

  • owner changed from adrian to mtredinnick.
  • status changed from assigned to new.

05/19/07 21:25:49 changed by Marc Fargas <telenieko@telenieko.com>

  • attachment 2306.diff added.

Just add a note on database backend notes.

05/19/07 21:27:35 changed by Marc Fargas <telenieko@telenieko.com>

  • has_patch set to 1.

Does this only affec 3.23.x ? If so, as the documentation recommends 4.1 or 5.0 we can place a warning about that (2306.diff does so) and either leave the ticket open in case someone wants to deal with it anyway or close it as "you have been warned" ;)

05/19/07 21:42:58 changed by russellm

  • has_patch deleted.

The problem isn't with the MySQL version - it's with the SQL that is generated. We shouldn't be doing a LEFT INNER JOIN in this situation. Every database will have the problem - it's just particularly obvious on old MySQL's due to the speed at which joins are performed. The upcoming query.py refactor should address this issue.

09/13/07 16:35:59 changed by mtredinnick

  • keywords set to qs-rf-fixed.

09/29/07 03:48:11 changed by anonymous

seduction Here's my post on how to get laid in the next month or two. This post does not maximize your chances of "getting really good at pickup" - in fact, it does the opposite.

04/26/08 21:50:16 changed by mtredinnick

  • status changed from new to closed.
  • resolution set to fixed.

(In [7477]) Merged the queryset-refactor branch into trunk.

This is a big internal change, but mostly backwards compatible with existing code. Also adds a couple of new features.

Fixed #245, #1050, #1656, #1801, #2076, #2091, #2150, #2253, #2306, #2400, #2430, #2482, #2496, #2676, #2737, #2874, #2902, #2939, #3037, #3141, #3288, #3440, #3592, #3739, #4088, #4260, #4289, #4306, #4358, #4464, #4510, #4858, #5012, #5020, #5261, #5295, #5321, #5324, #5325, #5555, #5707, #5796, #5817, #5987, #6018, #6074, #6088, #6154, #6177, #6180, #6203, #6658

Add/Change #2306 (Many-to-many queries very slow on MySQL 3.23.58)




Change Properties
Action