Django talk: Re: A lots of foreign keys

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`)  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

On Wednesday, 26 September 2012 01:25:05 UTC-4, Jani Tiainen wrote:

26.9.2012 3:08, rentgeeen kirjoitti:
> I did that already, removed the field_to and make them as normal primary
> keys,
>
> the problem is when I put FK fields into "list_display" admin it still
> takes SQL 60 secs to process, if I remove them all is ok and App works
> great.
>
> so weird
>
> On Tuesday, 25 September 2012 02:01:17 UTC-4, Jani Tiainen wrote:
>
> Actually problem exists in your model. Unless this is legacy database
> that you can't do anything about.
>
> Major performance killer is done by defining all your foreign key
> fields
> to be _strings_. Yes. "field_to" in model.ForeignKey() means that this
> field uses "field_to" in related model as a key. And that being a
> string
> is major performance killer.
>
> Secondly your string fields are not indexed and using string as a
> foreign key is not efficient in both terms space and performance.
>
> If you don't specify one of your fields as primary key Django creates
> one for you - called "id". If you don't specify "field_to" Django uses
> primary key from your model in most of the cases that is "id", which is
> integer. Indexed properly and so on.
>
> Try changing all your models to "correct" and remove "field_to"
> definitions from foreign keys. You should see quite a major speedup
> after rebuilding the database.
>
> 25.9.2012 2:17, rentgeeen kirjoitti:
> > Have a SQL problem, adding this model all works correctly, the
> problem
> > is in ADMIN.
> >
> > When I add the data just few to each table, by clicking on TYPE &
> PAGE
> > in ADMIN the page is loading so slow, installed debug_toolbar and
> SQL
> > took 17 seconds for the TYPE. When I tried the PAGE it gave me
> timeout,
> > my question is what is wrong with my model? Is it constructed bad?
> >
> > My goal is this lets say example:
> >
> > www.example.com/audi/4doors/s4/sport/red/audi-url
> <http://www.example.com/audi/4doors/s4/sport/red/audi-url>
> >
> > basically all 6 urls are dynamic that I would specify in the each
> table
> > and would be in the PAGE as dropdowns also in others. What is the
> > optimal way to do that or optimize the model?
> >
> > Here is a screenshot of TYPE page loading:
> >
> > screenshot: http://cl.ly/image/2931040E0t35
> <http://cl.ly/image/2931040E0t35>
> >
> > Records:
> >
> > auto_client = 3 rows
> >
> > auto_category = 2 rows
> >
> > auto_subcategory = 2 rows
> >
> > auto_project = 5 rows
> >
> > auto_type = 2 rows
> >
> > auto_page = 0 - because cliking on auto_page it times out because
> of SQL
> > query. Basically togehter like 14 records thats nothing :)
> >
> > here is also mysql query in PHPmyadmin: cl.ly/image/2S320h3d0P0J
> <http://cl.ly/image/2S320h3d0P0J>
> > <http://cl.ly/image/2S320h3d0P0J
> <http://cl.ly/image/2S320h3d0P0J>> 17 seconds
> >
> > Please help thanks
> >
> >
> > |from django.db import models
> >
> >
> > class Client(models.Model):
> > title= models.CharField(max_length=100, unique=True)
> > def __unicode__(self):
> > return self.title
> >
> > class Category(models.Model):
> > client= models.ForeignKey(Client, to_field='title')
> > title= models.CharField(max_length=200, unique=True)
> > def __unicode__(self):
> > return self.title
> >
> > class Subcategory(models.Model):
> > client= models.ForeignKey(Client, to_field='title')
> > category= models.ForeignKey(Category, to_field='title')
> > title= models.CharField(max_length=200, unique=True)
> > def __unicode__(self):
> > return self.title
> >
> > class Project(models.Model):
> > client= models.ForeignKey(Client, to_field='title')
> > category= models.ForeignKey(Category, to_field='title')
> > subcategory= models.ForeignKey(Subcategory, to_field='title')
> > title= models.CharField(max_length=200, unique=True)
> > def __unicode__(self):
> > return self.title
> >
> > class Type(models.Model):
> > client= models.ForeignKey(Client, to_field='title')
> > category= models.ForeignKey(Category, to_field='title')
> > subcategory= models.ForeignKey(Subcategory, to_field='title')
> > project= models.ForeignKey(Project, to_field='title')
> > title= models.CharField(max_length=200, unique=True)
> > def __unicode__(self):
> > return self.title
> >
> > class Page(models.Model):
> > client= models.ForeignKey(Client, to_field='title')
> > category= models.ForeignKey(Category, to_field='title')
> > subcategory= models.ForeignKey(Subcategory, to_field='title')
> > project= models.ForeignKey(Project, to_field='title')
> > type= models.ForeignKey(Type, to_field='title')
> > pageurl= models.CharField(max_length=200)|
> >
> > By cliking on TYPE this is the SQL output that takes 17 secs,
> cant click on last PAGE because thats so long - timed out:
> >
> > |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`)
> > 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|
> >
> > --
> > 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/-/C_zR-hp12oUJ
> <https://groups.google.com/d/msg/django-users/-/C_zR-hp12oUJ>.
> > To post to this group, send email to django...@googlegroups.com
> <javascript:>.
> > To unsubscribe from this group, send email to
> > django-users...@googlegroups.com <javascript:>.
> > For more options, visit this group at
> > http://groups.google.com/group/django-users?hl=en
> <http://groups.google.com/group/django-users?hl=en>.
>
>
> --
> 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/-/2Mgqv-X6igkJ.
> To post to this group, send email to django...@googlegroups.com.
> To unsubscribe from this group, send email to
> django-users...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/django-users?hl=en.

Does your queries run one by one for FK's or do you use select_related()?

Also could you get query/queries that is taking long to execute?

And what is your backend?

--
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/-/NUDt7pFYPzoJ.
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.

Django talk

Tuesday, September 25, 2012

Re: A lots of foreign keys - Django Admin

No comments:

Post a Comment

Followers

Blog Archive

About Me