etianen/django-reversion — pull request #118 by etienned (2012-01-11 04:12:47+00:00)
Title: Recover view trigger an infinite query (on MySQL at least...)

Hi Dave,

With version 1.5.1 on MySQL (InnoDB) when requesting the recover view, the DB start to take a lot and CPU and seems to never stop (I always restarted MySQL after a few minutes). So I looked at the code and the problem was coming from the `annotate(Max('latest_pk'))` in `get_deleted` (line 554).

Honestly, I didn't understand why this annotate was necessary? After a second look, the `get_deleted` function seems to me pretty complicated for nothing (but I can miss something). So I rebuild it in a simpler way, without the `annotate` and with simpler queries (only one with a subquery in it, if the DB support it, or 2, if not). It doesn't matter if the pk are int or not.

You query in SQL (MySQL) was mostly like this:

('SELECT U0.`id` FROM `reversion_version` U0 INNER JOIN `reversion_revision` U1 ON (U0.`revision_id` = U1.`id`) WHERE (U1.`manager_slug` = %s AND U0.`id` IN (SELECT MAX(U0.`id`) AS `latest_pk` FROM `reversion_version` U0 INNER JOIN `reversion_revision` U1 ON (U0.`revision_id` = U1.`id`) WHERE (U1.`manager_slug` = %s AND U0.`content_type_id` = %s AND NOT ((U0.`object_id_int` IN (SELECT U0.`id` FROM `my_model` U0) AND U0.`object_id_int` IS NOT NULL)) AND NOT (U0.`type` = %s )) GROUP BY U0.`object_id_int`))', ('default', 'default', 62, 2))

My query is like this :

('SELECT U0.`id` FROM `reversion_version` U0 INNER JOIN `reversion_revision` U1 ON (U0.`revision_id` = U1.`id`) WHERE (U1.`manager_slug` = %s AND U0.`content_type_id` = %s AND NOT (U0.`object_id` IN (SELECT U0.`id` FROM `my_model` U0)) AND NOT (U0.`type` = %s ))', ('default', 62, 2))

Look to work fine for me but I can definitely miss something. Maybe all this complexity was motivated by the `GROUP BY` necessary for a case that I don't see?