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-users@googlegroups.com.
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en.
