Author: mtredinnick
Date: 2008-12-09 23:19:27 -0600 (Tue, 09 Dec 2008)
New Revision: 9637
Modified:
django/trunk/django/db/backends/__init__.py
django/trunk/django/db/backends/mysql/base.py
django/trunk/django/db/models/sql/query.py
django/trunk/tests/regressiontests/queries/models.py
Log:
If an SQL query doesn't specify any ordering, avoid the implicit sort
that happens with MySQL when a "GROUP BY" clause is included. This is a
backend-specific operation, so any other databases requiring similar
encouragement can have a function added to their own backend code.
Modified: django/trunk/django/db/backends/__init__.py
===================================================================
--- django/trunk/django/db/backends/__init__.py 2008-12-10 01:53:02 UTC (rev
9636)
+++ django/trunk/django/db/backends/__init__.py 2008-12-10 05:19:27 UTC (rev
9637)
@@ -143,6 +143,14 @@
"""
return '%s'
+ def force_no_ordering(self):
+ """
+ Returns a list used in the "ORDER BY" clause to force no ordering at
+ all. Returning an empty list means that nothing will be included in the
+ ordering.
+ """
+ return []
+
def fulltext_search_sql(self, field_name):
"""
Returns the SQL WHERE clause to use in order to perform a full-text
Modified: django/trunk/django/db/backends/mysql/base.py
===================================================================
--- django/trunk/django/db/backends/mysql/base.py 2008-12-10 01:53:02 UTC
(rev 9636)
+++ django/trunk/django/db/backends/mysql/base.py 2008-12-10 05:19:27 UTC
(rev 9637)
@@ -133,6 +133,14 @@
def drop_foreignkey_sql(self):
return "DROP FOREIGN KEY"
+ def force_no_ordering(self):
+ """
+ "ORDER BY NULL" prevents MySQL from implicitly ordering by grouped
+ columns. If no ordering would otherwise be applied, we don't want any
+ implicit sorting going on.
+ """
+ return ["NULL"]
+
def fulltext_search_sql(self, field_name):
return 'MATCH (%s) AGAINST (%%s IN BOOLEAN MODE)' % field_name
Modified: django/trunk/django/db/models/sql/query.py
===================================================================
--- django/trunk/django/db/models/sql/query.py 2008-12-10 01:53:02 UTC (rev
9636)
+++ django/trunk/django/db/models/sql/query.py 2008-12-10 05:19:27 UTC (rev
9637)
@@ -288,6 +288,8 @@
if self.group_by:
grouping = self.get_grouping()
result.append('GROUP BY %s' % ', '.join(grouping))
+ if not ordering:
+ ordering = self.connection.ops.force_no_ordering()
if self.having:
having, h_params = self.get_having()
Modified: django/trunk/tests/regressiontests/queries/models.py
===================================================================
--- django/trunk/tests/regressiontests/queries/models.py 2008-12-10
01:53:02 UTC (rev 9636)
+++ django/trunk/tests/regressiontests/queries/models.py 2008-12-10
05:19:27 UTC (rev 9637)
@@ -6,6 +6,7 @@
import pickle
import sys
+from django.conf import settings
from django.db import models
from django.db.models.query import Q, ITER_CHUNK_SIZE
@@ -1053,3 +1054,20 @@
[]
"""
+
+if settings.DATABASE_ENGINE == "mysql":
+ __test__["API_TESTS"] += """
+When grouping without specifying ordering, we add an explicit "ORDER BY NULL"
+portion in MySQL to prevent unnecessary sorting.
+
+>>> query = Tag.objects.values_list('parent_id', flat=True).order_by().query
+>>> query.group_by = ['parent_id']
+>>> sql = query.as_sql()[0]
+>>> fragment = "ORDER BY "
+>>> pos = sql.find(fragment)
+>>> sql.find(fragment, pos + 1) == -1
+True
+>>> sql.find("NULL", pos + len(fragment)) == pos + len(fragment)
+True
+
+"""
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/django-updates?hl=en
-~----------~----~----~----~------~----~------~--~---
