#25470: Django cast DATE field to DATETIME unexpectedly
-------------------------------------+-------------------------------------
Reporter: stanleyxu2005 | Owner: nobody
Type: | Status: new
Cleanup/optimization |
Component: Database layer | Version: 1.8
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Description changed by stanleyxu2005:
Old description:
> 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 = 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"}
> }}}
>
> 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?
>
> Currently, I use a workaround:
> {{{
> db_dates = [dt['date'] for dt in
> Model1.objects.order_by('date').values('date').distinct()]
> }}}
New description:
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 a workaround:
{{{
db_dates = Model1.objects.values_list('date', flat=True).distinct()
}}}
I posted a question at [http://stackoverflow.com/questions/32795047/have-a
-perforamance-issue-of-query-date-object-using-django-queryset
StackOverflow].
--
--
Ticket URL: <https://code.djangoproject.com/ticket/25470#comment:6>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/071.72f801ed09c34aa34401907dc7d1dd6b%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
