Serious performance impact (500x slower!) when querying distinct model dates objects

[Qian Xu]

I'm using django 1.8.4 writing a webapp. The backend uses MySQL 5.6 (MyISAM). Recently the number of table records reaches 1 million, it will take 1-1.5 seconds to query all distinct record dates. But using MySQL client, it takes less than 0.001 second.

Django Code

class Model1(models.Model):
    date = models.DateField(db_index=True)

# benchmark code
db_dates = Model1.objects.dates("date", kind="day")

I dumped the django queries.

{u'time': u'1.989', u'sql': u"SELECT DISTINCT CAST(DATE_FORMAT(`app1_model1`.`date`, '%Y-%m-%d 00:00:00') AS DATETIME) AS `datefield` FROM `app1_model1` WHERE `app1_model1`.`date` IS NOT NULL ORDER BY `datefield` ASC"}

As you can see, the query did possibly an unnecessary type cast. The performance impact is scaled to the amount of records. The table field is exactly DATE type. I don't know, if there is some reason for the type casting.

Currently, I use another query to get these dates, which takes 0.04 second.

db_dates = Model1.objects.values_list('date', flat=True).distinct()

I posted a question at ​StackOverflow.

[Tim Graham]

If you'd like to investigate and propose a fix, that would be great!

[Qian Xu]

Thanks for agreeing this is a serious performance impact. It is still unclear, why DATE is first converted to DATETIME. The Django team must know, whether the code is written by design?

[Tim Graham]

Did you try modifying the code and seeing if any tests fail? That's often helpful.

[Qian Xu]

Replying to timgraham:

Did you try modifying the code and seeing if any tests fail? That's often helpful.

I dont have the time to do any further investigation. I don't realize that when I report issue to django community, I'd provide patch and do necessary tests. Really sorry about that. Bye.

[Mariusz Felisiak]

I made ​PR to omit unnecessary type cast.

In my opinion, the second step, is to change default order in dates, datetimes methods

django/db/models/query.py

@@ -726 +726 @@
         clone._iterable_class = FlatValuesListIterable if flat else ValuesListIterable
         return clone
 
-    def dates(self, field_name, kind, order='ASC'):
+    def dates(self, field_name, kind, order=None):
         """

or give a way to disable it

django/db/models/query.py

@@ -733 +733 @@
         """
         assert kind in ("year", "month", "day"), \
             "'kind' must be one of 'year', 'month' or 'day'."
-        assert order in ('ASC', 'DESC'), \
-            "'order' must be either 'ASC' or 'DESC'."
-        return self.annotate(
+        assert order is None or order in ('ASC', 'DESC'), \
+            "'order' must be either 'ASC', 'DESC' or None."
+        qs = self.annotate(
             datefield=Date(field_name, kind),
             plain_field=F(field_name)
         ).values_list(
             'datefield', flat=True
-        ).distinct().filter(plain_field__isnull=False).order_by(('-' if order == 'DESC' else '') + 'datefield')
+        ).distinct().filter(plain_field__isnull=False)
+        if order is not None:
+            return qs.order_by(('-' if order == 'DESC' else '') + 'datefield')
+        else:
+            return qs

[Mariusz Felisiak]

Any suggestions? I can't find any indications of default sort.

[Simon Charette]

The default sort order of ASC seems ​to be documented and simply making results unordered would be backward incompatible.

I guess you should open a new ticket for a feature request allowing the use of order=None to get unordered results.

[Tim Graham <timograham@…>]

In 0f6d51e6:

Fixed #25470 -- Avoided unnecessary, expensive DATETIME typecast on MySQL.