From: Marek Stępniowski Date: Tue, 9 Sep 2008 21:21:20 +0000 (+0200) Subject: Added hack to SQL in TagManager.related_for_model to prevent severe performance loss... X-Git-Url: https://git.mdrn.pl/wolnelektury.git/commitdiff_plain/fe3838bfb645ccb7e2775ae709ac45044fd14d51?ds=inline;hp=ad52d2dda38fe645613acdd2c3b9de006cb58cf9 Added hack to SQL in TagManager.related_for_model to prevent severe performance loss in MySQL. --- diff --git a/apps/newtagging/models.py b/apps/newtagging/models.py index 5385e9506..e988b6c9d 100644 --- a/apps/newtagging/models.py +++ b/apps/newtagging/models.py @@ -217,22 +217,26 @@ class TagManager(models.Manager): if 'where' in extra: extra_where = 'AND ' + ' AND '.join(extra['where']) + # Temporary table in this query is a hack to prevent MySQL from executing + # inner query as dependant query (which could result in severe performance loss) query = """ SELECT %(tag_columns)s%(count_sql)s FROM %(tagged_item)s INNER JOIN %(tag)s ON %(tagged_item)s.tag_id = %(tag)s.id WHERE %(tagged_item)s.content_type_id = %(content_type_id)s - AND %(tagged_item)s.object_id IN - ( - SELECT %(tagged_item)s.object_id - FROM %(tagged_item)s, %(tag)s - WHERE %(tagged_item)s.content_type_id = %(content_type_id)s - AND %(tag)s.id = %(tagged_item)s.tag_id - AND %(tag)s.id IN (%(tag_id_placeholders)s) - GROUP BY %(tagged_item)s.object_id - HAVING COUNT(%(tagged_item)s.object_id) = %(tag_count)s - ) - AND %(tag)s.id NOT IN (%(tag_id_placeholders)s) - %(extra_where)s + AND %(tagged_item)s.object_id IN + ( + SELECT temporary.object_id + FROM ( + SELECT %(tagged_item)s.object_id + FROM %(tagged_item)s, %(tag)s + WHERE %(tagged_item)s.content_type_id = %(content_type_id)s + AND %(tag)s.id = %(tagged_item)s.tag_id + AND %(tag)s.id IN (%(tag_id_placeholders)s) + GROUP BY %(tagged_item)s.object_id + HAVING COUNT(%(tagged_item)s.object_id) = %(tag_count)s + ) AS temporary + ) + %(extra_where)s GROUP BY %(tag_columns)s %(min_count_sql)s ORDER BY %(tag)s.%(ordering)s ASC""" % {