Improve queries on django admin

[Rael Max]

Hi everyone,

I'm working in a project with a large mysql database and i've faced with problems generated on django admin list. Basically, the query executed to retrieve a list of items from a model uses a SQL SELECT passing a list of all attributes of model, but usually we only use a small set of them on list_display attribute.

I solved this problem overriding the queryset method of ModelAdmin and using the method only of QuerySet using the fields listed on list_display attribute of ModelAdmin. With the limit of columns retrieved this queries should to consume less memory to be executed.

Searching about this here and on django issue tracker i've not found nothing about. What you think about this optimization be the default behavior or use a ModelAdmin attribute to enable?

Regards,

Rael

[Lucas Magnum]

You can use `list_select_related` for Django Admin too.

[]'s

Lucas Magnum.

--
You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-develop...@googlegroups.com.
To post to this group, send email to django-d...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-developers.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/0fab70ef-2217-4069-9f37-2ec2376626c6%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Rael Max]

Hi Lucas, thanks for reply

I think that select_related gives a great improve on performance but we can improve his usage passing the columns that we want retrieve, avoiding getting most columns/data and allocate more memory than necessary.

[Tim Graham]

I'm a bit wary of the complexity this would add, especially given this warning in the documentation:

The defer() method (and its cousin, only(), below) are only for advanced use-cases. They provide an optimization for when you have analyzed your queries closely and understand exactly what information you need and have measured that the difference between returning the fields you need and the full set of fields for the model will be significant.

I think it's better if developers override ModelAdmin.get_queryset() as needed, as you've done. In particular, I thought of the case where a method is used in list_display. In that case, I believe it's impossible to do the optimization automatically since Django can't automatically determine what fields a method might use.

[Shai Berger]

I tend to agree with Tim -- in particular, a query on the admin should only
return a small (<100) number of records, due to paging; if, for that size of
query, you see a significant difference between returning all the columns and
returning just the ones you need, it is suspicious: Either you have a very
special case (e.g. it just happens that all the fields you chose to list are in
an index, and so are selected extremely fast), or you're doing something wrong
(which causes your default query to be very slow).

For most cases, this should not be a significant optimization IMO.

On Friday 22 July 2016 20:38:40 Tim Graham wrote:
> I'm a bit wary of the complexity this would add, especially given this
> warning in the documentation:
>
> The defer() method (and its cousin, only()

> <https://docs.djangoproject.com/en/1.9/ref/models/querysets/#django.db.mode
> ls.query.QuerySet.only>, below) are only for advanced use-cases. They

> provide an optimization for when you have analyzed your queries closely

> and understand *exactly* what information you need and have measured that

> the difference between returning the fields you need and the full set of
> fields for the model will be significant.
>
> I think it's better if developers override ModelAdmin.get_queryset() as
> needed, as you've done. In particular, I thought of the case where a method
> is used in list_display. In that case, I believe it's impossible to do the
> optimization automatically since Django can't automatically determine what
> fields a method might use.
>
> On Friday, July 22, 2016 at 9:51:27 AM UTC-4, Rael Max wrote:
> > Hi Lucas, thanks for reply
> >
> > I think that select_related gives a great improve on performance but we
> > can improve his usage passing the columns that we want retrieve, avoiding
> > getting most columns/data and allocate more memory than necessary.
> >
> > Em quinta-feira, 21 de julho de 2016 17:01:00 UTC-3, Lucas Magnum
escreveu:
> >> You can use `list_select_related` for Django Admin too.
> >>
> >>
> >>
> >>
> >> []'s
> >>
> >> Lucas Magnum.
> >>
> >> 2016-07-21 15:52 GMT-03:00 Rael Max <ozkon...@gmail.com>:
> >>> Hi everyone,
> >>>
> >>> I'm working in a project with a large mysql database and i've faced
> >>> with problems generated on django admin list. Basically, the query
> >>> executed to retrieve a list of items from a model uses a SQL SELECT
> >>> passing a list of all attributes of model, but usually we only use a

> >>> small set of them on *list_display* attribute.
> >>>
> >>> I solved this problem overriding the *queryset* method of *ModelAdmin*
> >>> and using the method only of *QuerySet* using the fields listed on
> >>> *list_display* attribute of *ModelAdmin*. With the limit of columns

> >>> retrieved this queries should to consume less memory to be executed.
> >>>
> >>> Searching about this here and on django issue tracker i've not found
> >>> nothing about. What you think about this optimization be the default

> >>> behavior or use a *ModelAdmin* attribute to enable?
> >>>
> >>> Regards,
> >>> Rael

[Tobias McNulty]

I think the question was more about memory usage than speed, but the answer is the same, in my opinion.

The only thing I'll add is that -- if returning a mere 100 rows of a table is really causing memory issues -- there are alternatives to storing large blobs of data directly in the DB (e.g., files).

Tobias

--

Tobias McNulty
Chief Executive Officer

tob...@caktusgroup.com
www.caktusgroup.com