Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Django admin makes a lot of duplicate queries in mysql

I have encountered a problem.

When I try to open the change page in order to see parameters within element (like djangosite.com/admin/djangoapp/someelement/1/change/) it loads very slowly (10-15 seconds).

I found out that Django is making a lot of duplicate queries:

UPDATE: I think I made mistake in admin.py.

   inlines = [PhoneInline,FlatInline,NeedInline]

If i delete line above everything is fine.

mysql log

2161 Query  set autocommit=0
         2161 Query SELECT `ha_phone`.`id`, `ha_phone`.`phone_number`, `ha_phone`.`phone_owner_id` FROM `ha_phone` WHERE `ha_phone`.`id` = 262
         2161 Query SELECT `django_content_type`.`id`, `django_content_type`.`app_label`, `django_content_type`.`model` FROM `django_content_type` WHERE (`django_content_type`.`model` = 'phone' AND `django_content_type`.`app_label` = 'MYAPP')
         2161 Query commit
         2161 Query set autocommit=1
         2161 Query SELECT `ha_owner`.`id`, `ha_owner`.`owner_pub_date`, `ha_owner`.`owner_name`, `ha_owner`.`owner_verify`, `ha_owner`.`owner_company_id` FROM `ha_owner` WHERE `ha_owner`.`id` = 236
         2161 Query SELECT `ha_company`.`id`, `ha_company`.`company_name` FROM `ha_company` WHERE `ha_company`.`id` = 1
         2161 Query SELECT `ha_owner`.`id`, `ha_owner`.`owner_pub_date`, `ha_owner`.`owner_name`, `ha_owner`.`owner_verify`, `ha_owner`.`owner_company_id` FROM `ha_owner`
         2161 Query SELECT `ha_company`.`id`, `ha_company`.`company_name` FROM `ha_company` WHERE `ha_company`.`id` = 1
         2161 Query SELECT `ha_company`.`id`, `ha_company`.`company_name` FROM `ha_company` WHERE `ha_company`.`id` = 1
         2161 Query SELECT `ha_company`.`id`, `ha_company`.`company_name` FROM `ha_company` WHERE `ha_company`.`id` = 1
         2161 Query SELECT `ha_company`.`id`, `ha_company`.`company_name` FROM `ha_company` WHERE `ha_company`.`id` = 1
         2161 Query SELECT `ha_company`.`id`, `ha_company`.`company_name` FROM `ha_company` WHERE `ha_company`.`id` = 1
         2161 Query SELECT `ha_company`.`id`, `ha_company`.`company_name` FROM `ha_company` WHERE `ha_company`.`id` = 1
         2161 Query SELECT `ha_company`.`id`, `ha_company`.`company_name` FROM `ha_company` WHERE `ha_company`.`id` = 1
         2161 Query SELECT `ha_company`.`id`, `ha_company`.`company_name` FROM `ha_company` WHERE `ha_company`.`id` = 1
         2161 Query SELECT `ha_company`.`id`, `ha_company`.`company_name` FROM `ha_company` WHERE `ha_company`.`id` = 1

...

models.py

class House(models.Model):
    house_number = models.CharField(max_length=10, verbose_name="Номер дома")
    house_floors = models.PositiveSmallIntegerField(verbose_name="Число этажей", null=True)
    house_walls = models.ForeignKey(Walls, verbose_name="Материал стен", null=True)
    house_street = models.ForeignKey(Street, verbose_name="Улица")
    house_metro = models.ForeignKey(Metro, verbose_name="Станция Метро", null=True)
    house_block = models.ForeignKey(Block, verbose_name="Микрорайон", null=True)


    class Meta:
        verbose_name = 'Дом'
        verbose_name_plural = 'Дома'
        ordering = ['house_street','house_number']


    def __unicode__(self):
        return unicode(self.house_street.street_name) + " " + unicode(self.house_number)

class Flat(models.Model):
    flat_number = models.CharField(max_length=7, verbose_name="Номер квартиры", null=True)
    flat_rooms = models.PositiveSmallIntegerField(verbose_name="Число комнат")
    flat_total_sq = models.PositiveSmallIntegerField(verbose_name="Общая площадь")
    flat_life_sq = models.PositiveSmallIntegerField(verbose_name="Жилая площадь")
    flat_kitchen_sq = models.PositiveSmallIntegerField(verbose_name="Кухонная площадь")
    flat_floors = models.PositiveSmallIntegerField(verbose_name="Этаж")
    flat_house = models.ForeignKey(House, verbose_name="Дом")
    flat_owner = models.ForeignKey(Owner, verbose_name="Владелец")
    flat_comment = models.TextField(verbose_name="Комметарий", null=True)
    flat_price = models.PositiveIntegerField(verbose_name='Цена квартиры')
...    
class Owner(models.Model):
        owner_pub_date = models.DateField(default=None,null=True,verbose_name="Дата публикации")
        owner_name = models.CharField(max_length=150, verbose_name="ФИО владельца")
        owner_verify = models.BooleanField(verbose_name="Пользователь верифицирован?")
        owner_company = models.ForeignKey(Company, verbose_name="Компания")

        class Meta:
            verbose_name = 'Владельца'
            verbose_name_plural = 'Владельцы'

        def __unicode__(self):
            return unicode(self.id) + " | "+ self.owner_name + " " + unicode(self.owner_company)


    class Phone(models.Model):
        phone_number = models.CharField(max_length=25, verbose_name="Номер телефона")
        phone_owner = models.ForeignKey(Owner, verbose_name="Владелец телефона")

        class Meta:
            verbose_name = 'Телефон'
            verbose_name_plural = 'Телефоны'

        def __unicode__(self):
            return unicode(self.phone_owner) + " " + self.phone_number

admin.py

...        
class PhoneInline(admin.StackedInline):
            model = Phone
            extra = 2

        class FlatInline(admin.TabularInline):
            model = Flat
            extra = 0

        class NeedInline(admin.TabularInline):
            model = Need
            extra = 1

        class OwnerAdmin(admin.ModelAdmin):
            field=['owner_name']
            inlines = [PhoneInline,FlatInline,NeedInline]

    admin.site.register(Owner,OwnerAdmin)

What should I do? Thank you very much.

UPDATE @Alasdair, thank you. Queries with ha_company are reduced. But I have another duplicates with ha_street. Looks like this:

 2601 Query SELECT `ha_company`.`id`, `ha_company`.`company_name` FROM `ha_company`
         2601 Query SELECT `ha_house`.`id`, `ha_house`.`house_number`, `ha_house`.`house_floors`, `ha_house`.`house_walls_id`, `ha_house`.`house_street_id`, `ha_house`.`house_metro_id`, `ha_house`.`house_block_id`, `ha_house`.`house_age`, `ha_house`.`house_flat`, `ha_house`.`house_quality` FROM `ha_house` INNER JOIN `ha_street` ON (`ha_house`.`house_street_id` = `ha_street`.`id`) ORDER BY `ha_street`.`street_name` ASC, `ha_house`.`house_number` ASC
         2601 Query SELECT `ha_street`.`id`, `ha_street`.`street_name` FROM `ha_street` WHERE `ha_street`.`id` = 74
         2601 Query SELECT `ha_street`.`id`, `ha_street`.`street_name` FROM `ha_street` WHERE `ha_street`.`id` = 74
         2601 Query SELECT `ha_street`.`id`, `ha_street`.`street_name` FROM `ha_street` WHERE `ha_street`.`id` = 36
         2601 Query SELECT `ha_street`.`id`, `ha_street`.`street_name` FROM `ha_street` WHERE `ha_street`.`id` = 582
         2601 Query SELECT `ha_street`.`id`, `ha_street`.`street_name` FROM `ha_street` WHERE `ha_street`.`id` = 582
like image 992
mailman_73 Avatar asked Mar 13 '23 23:03

mailman_73


1 Answers

The __unicode__ method for the Phone model includes unicode(self.phone_owner), which in turn accesses the owner_company foreign key. That means that Django has to look up the company for every phone object in the inline.

You fixed the problem by removing the inline, but if you want to keep the inline you could either:

  1. Change the __unicode__ method for Phone or Owner so that the company is not included
  2. Override the get_queryset method for your inline, so that you can use select_related.

    class PhoneInline(admin.StackedInline):
        model = Phone
        extra = 2
    
        def get_queryset(self, request):
            return super(PhoneInline, self).get_queryset(request).select_related('phone_owner', 'phone_owner__ owner_company')
    
like image 185
Alasdair Avatar answered Mar 16 '23 09:03

Alasdair