Opened 9 years ago

Last modified 9 years ago

#25470 closed Cleanup/optimization

Django cast DATE field to DATETIME unexpectedly — at Version 1

Reported by: Qian Xu Owned by: nobody
Component: Database layer (models, ORM) Version: 1.8
Severity: Normal Keywords:
Cc: felisiak.mariusz@…, Simon Charette Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Qian Xu)

I posted a question at http://stackoverflow.com/questions/32795047/have-a-perforamance-issue-of-query-date-object-using-django-queryset

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 = set(Model1.objects.dates("date", kind="day"))

I dumped the django queries.

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

I noticed that the second query did a type cast. The cast is not a necessary step. It slows down when the amount of records is huge. I pretty sure this is the root cause of the slow.

The table is created by django. The field is exactly DATE field. I'd like to know, why django cast DATE to DATETIME. Is it a bug?

Change History (1)

comment:1 by Qian Xu, 9 years ago

Description: modified (diff)
Note: See TracTickets for help on using tickets.
Back to Top