Re: A lots of foreign keys - Django Admin

Also how can I use "select_related()" for admin?

Should I put it in admin.py or settings like a setting or how?

thanks

On Wednesday, 26 September 2012 10:16:29 UTC-4, rentgeeen wrote:

Here is mysql profiling:

Results:

Query:

http://cl.ly/image/0311392u0Z0S

CPU Profile:

http://cl.ly/image/2D210h0f1L06

On Wednesday, 26 September 2012 09:38:03 UTC-4, rentgeeen wrote:

If you want I can send you a zip of the project 

On Wednesday, 26 September 2012 09:34:33 UTC-4, rentgeeen wrote:

Here is the SQL again it overlap in previous post dont know why it posted twice:

SELECT `auto_type`.`id`, `auto_type`.`client_id`, `auto_type`.`category_id`, `auto_type`.`subcategory_id`, `auto_type`.`project_id`, `auto_type`.`title`, `auto_client`.`id`, `auto_client`.`title`, `auto_category`.`id`, `auto_category`.`client_id`, `auto_category`.`title`, T4.`id`, T4.`title`, `auto_subcategory`.`id`, `auto_subcategory`.`client_id`, `auto_subcategory`.`category_id`, `auto_subcategory`.`title`, T6.`id`, T6.`title`, T7.`id`, T7.`client_id`, T7.`title`, T8.`id`, T8.`title`, `auto_project`.`id`, `auto_project`.`client_id`, `auto_project`.`category_id`, `auto_project`.`subcategory_id`, `auto_project`.`title`, T10.`id`, T10.`title`, T11.`id`, T11.`client_id`, T11.`title`, T12.`id`, T12.`title`, T13.`id`, T13.`client_id`, T13.`category_id`, T13.`title`, T14.`id`, T14.`title`, T15.`id`, T15.`client_id`, T15.`title`, T16.`id`, T16.`title` FROM `auto_type` INNER JOIN `auto_client` ON (`auto_type`.`client_id` = `auto_client`.`id`) INNER JOIN `auto_category` ON(`auto_type`.`category_id` = `auto_category`.`id`) INNER JOIN `auto_client` T4 ON (`auto_category`.`client_id` = T4.`id`) INNER JOIN `auto_subcategory` ON(`auto_type`.`subcategory_id` = `auto_subcategory`.`id`) INNER JOIN `auto_client` T6 ON (`auto_subcategory`.`client_id` = T6.`id`) INNER JOIN`auto_category` T7 ON (`auto_subcategory`.`category_id` = T7.`id`) INNER JOIN `auto_client` T8 ON (T7.`client_id` = T8.`id`) INNER JOIN `auto_project` ON(`auto_type`.`project_id` = `auto_project`.`id`) INNER JOIN `auto_client` T10 ON (`auto_project`.`client_id` = T10.`id`) INNER JOIN `auto_category` T11 ON(`auto_project`.`category_id` = T11.`id`) INNER JOIN `auto_client` T12 ON (T11.`client_id` = T12.`id`) INNER JOIN `auto_subcategory` T13 ON(`auto_project`.`subcategory_id` = T13.`id`) INNER JOIN `auto_client` T14 ON (T13.`client_id` = T14.`id`) INNER JOIN `auto_category` T15 ON(T13.`category_id` = T15.`id`) INNER JOIN `auto_client` T16 ON (T15.`client_id` = T16.`id`) ORDER BY `auto_type`.`id` DESC

below are screenshots

On Wednesday, 26 September 2012 09:33:02 UTC-4, rentgeeen wrote:

I am sorry I post the old one here is the update:

Screenshot:

http://cl.ly/image/2A3z1q2l053l

Screenshot of Admin clicking on TYPES = 60 seconds to load this is the query

http://cl.ly/image/1G1S0c0r302s

if I remove from admin "list_display

class ProjectAdmin(admin.ModelAdmin):

list_display = ('client', 'category', 'subcategory', 'title', )

admin.site.register(Project, ProjectAdmin)

all foreign keys then in admin all works super fast.

SELECT `auto_type`.`id`, `auto_type`.`client_id`, `auto_type`.`category_id`, `auto_type`.`subcategory_id`, `auto_type`.`project_id`, `auto_type`.`title`, `auto_client`.`id`, `auto_client`.`title`, `auto_category`.`id`, `auto_category`.`client_id`, `auto_category`.`title`, T4.`id`, T4.`title`, `auto_subcategory`.`id`, `auto_subcategory`.`client_id`, `auto_subcategory`.`category_id`, `auto_subcategory`.`title`, T6.`id`, T6.`title`, T7.`id`, T7.`client_id`, T7.`title`, T8.`id`, T8.`title`, `auto_project`.`id`, `auto_project`.`client_id`, `auto_project`.`category_id`, `auto_project`.`subcategory_id`, `auto_project`.`title`, T10.`id`, T10.`title`, T11.`id`, T11.`client_id`, T11.`title`, T12.`id`, T12.`title`, T13.`id`, T13.`client_id`, T13.`category_id`, T13.`title`, T14.`id`, T14.`title`, T15.`id`, T15.`client_id`, T15.`title`, T16.`id`, T16.`title` FROM `auto_type` INNER JOIN `auto_client` ON (`auto_type`.`client_id` = `auto_client`.`id`) INNER JOIN `auto_category` ON(`auto_type`.`category_id` = `auto_category`.`id`) INNER JOIN `auto_client` T4 ON (`auto_category`.`client_id` = T4.`id`) INNER JOIN `auto_subcategory` ON(`auto_type`.`subcategory_id` = `auto_subcategory`.`id`) INNER JOIN `auto_client` T6 ON (`auto_subcategory`.`client_id` = T6.`id`) INNER JOIN`auto_category` T7 ON (`auto_subcategory`.`category_id` = T7.`id`) INNER JOIN `auto_client` T8 ON (T7.`client_id` = T8.`id`) INNER JOIN `auto_project` ON(`auto_type`.`project_id` = `auto_project`.`id`) INNER JOIN `auto_client` T10 ON (`auto_project`.`client_id` = T10.`id`) INNER JOIN `auto_category` T11 ON(`auto_project`.`category_id` = T11.`id`) INNER JOIN `auto_client` T12 ON (T11.`client_id` = T12.`id`) INNER JOIN `auto_subcategory` T13 ON(`auto_project`.`subcategory_id` = T13.`id`) INNER JOIN `auto_client` T14 ON (T13.`client_id` = T14.`id`) INNER JOIN `auto_category` T15 ON(T13.`category_id` = T15.`id`) INNER JOIN `auto_client` T16 ON (T15.`client_id` = T16.`id`) ORDER BY `auto_type`.`id` DESC

Clicking on PAGES time out cause it has more FKs

On Wednesday, 26 September 2012 03:59:02 UTC-4, Jani Tiainen wrote:

Your query clearly indicates that you're still doing foreign keys
against TITLE fields, which are strings. So something is definitely
still incorrect in your models.

Make sure that your foreign keys really point to ID field (basically
that is leaving out

26.9.2012 9:24, rentgeeen kirjoitti:
> I postet the query above in the 1st post, it takes like 17secs, 1 of
> them like 60 secs
>
> |SELECT `auto_type`.`id`, `auto_type`.`client_id`,
> `auto_type`.`category_id`, `auto_type`.`subcategory_id`,
> `auto_type`.`project_id`, `auto_type`.`title`, `auto_client`.`id`,
> `auto_client`.`title`, `auto_category`.`id`, `auto_category`.`client_id`,
> `auto_category`.`title`, T4.`id`, T4.`title`, `auto_subcategory`.`id`,
> `auto_subcategory`.`client_id`, `auto_subcategory`.`category_id`,
> `auto_subcategory`.`title`, T6.`id`, T6.`title`, T7.`id`, T7.`client_id`,
> T7.`title`, T8.`id`, T8.`title`, `auto_project`.`id`,
> `auto_project`.`client_id`, `auto_project`.`category_id`,
> `auto_project`.`subcategory_id`, `auto_project`.`title`, T10.`id`,
> T10.`title`, T11.`id`, T11.`client_id`, T11.`title`, T12.`id`,
> T12.`title`, T13.`id`, T13.`client_id`, T13.`category_id`, T13.`title`,
> T14.`id`, T14.`title`, T15.`id`, T15.`client_id`, T15.`title`, T16.`id`,
> T16.`title` FROM `auto_type` INNER JOIN `auto_client` ON
> (`auto_type`.`client_id` = `auto_client`.`title`) INNER JOIN
> `auto_category` ON (`auto_type`.`category_id` = `auto_category`.`title`)
> INNER JOIN `auto_client` T4 ON (`auto_category`.`client_id` = T4.`title`)
Above is join from auto_category to auto_client "title".

If your model would be "right" it should be autocategory.client_id = T4.id.


> INNER JOIN `auto_subcategory` ON (`auto_type`.`subcategory_id` =
> `auto_subcategory`.`title`) INNER JOIN `auto_client` T6 ON
> (`auto_subcategory`.`client_id` = T6.`title`) INNER JOIN `auto_category`
> T7 ON (`auto_subcategory`.`category_id` = T7.`title`) INNER JOIN
> `auto_client` T8 ON (T7.`client_id` = T8.`title`) INNER JOIN
> `auto_project` ON (`auto_type`.`project_id` = `auto_project`.`title`)
> INNER JOIN `auto_client` T10 ON (`auto_project`.`client_id` = T10.`title`)
> INNER JOIN `auto_category` T11 ON (`auto_project`.`category_id` =
> T11.`title`) INNER JOIN `auto_client` T12 ON (T11.`client_id` =
> T12.`title`) INNER JOIN `auto_subcategory` T13 ON
> (`auto_project`.`subcategory_id` = T13.`title`) INNER JOIN `auto_client`
> T14 ON (T13.`client_id` = T14.`title`) INNER JOIN `auto_category` T15 ON
> (T13.`category_id` = T15.`title`) INNER JOIN `auto_client` T16 ON
> (T15.`client_id` = T16.`title`) ORDER BY `auto_type`.`id` DESC|
>
> |what do you mean by back-end?|
>
> python, django, centos6 64bit server, mysql
>
> all works fine also on website + admin but only that part if I click each table in admin which are defined in admin.py
>
> |   |class ProjectAdmin(admin.ModelAdmin):
>
> |
>      list_display = ('client', 'category', 'subcategory', 'title', )
>
> admin.site.register(Project, ProjectAdmin)|
>
> |the more I add FK's to list display and you click that PROJECT table or others the view of it in admin is making that query and takes so long....if I make it just:|
>
> |  class ProjectAdmin(admin.ModelAdmin):
>      list_display = ( 'title', )
>
> admin.site.register(Project, ProjectAdmin)|
>
> NO FKs then it runs in 0.01ms
>
> Screenshot:
>
> http://cl.ly/image/3w3D3g0h3h1A
>

Also I wonder does admin run one query for each FK per row..

>
[snipsnip]


--
Jani Tiainen

- Well planned is half done and a half done has been sufficient before...

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To view this discussion on the web visit https://groups.google.com/d/msg/django-users/-/2zFqrlodvIgJ.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to django-users+unsubscribe@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-users?hl=en.