python之我的 Django 查询在终端上给我数据时速度很慢

我有一个用户表，其中包含 3 种类型的用户，学生、教职工和俱乐部，还有一张大学表。 我想要的是特定大学中有多少用户。 我得到了我想要的输出，但输出速度非常慢。我有 90k 用户，它生成的输出需要几分钟才能产生结果。

我的用户模型:-

from __future__ import unicode_literals 
from django.db import models 
from django.contrib.auth.models import User 
from cms.models.masterUserTypes import MasterUserTypes 
from cms.models.universities import Universities 
from cms.models.departments import MasterDepartments 
 
 
 
# WE ARE AT MODELS/APPUSERS 
 
requestChoice = ( 
    ('male', 'male'), 
    ('female', 'female'), 
    ) 
 
 
class Users(models.Model): 
    id = models.IntegerField(db_column="id", max_length=11, help_text="") 
    userTypeId = models.ForeignKey(MasterUserTypes, db_column="userTypeId") 
    universityId = models.ForeignKey(Universities, db_column="universityId")   
    departmentId = models.ForeignKey(MasterDepartments , db_column="departmentId",help_text="")   
    name = models.CharField(db_column="name",max_length=255,help_text="") 
    username = models.CharField(db_column="username",unique=True, max_length=255,help_text="") 
    email = models.CharField(db_column="email",unique=True, max_length=255,help_text="") 
    password = models.CharField(db_column="password",max_length=255,help_text="") 
    bio = models.TextField(db_column="bio",max_length=500,help_text="") 
    gender = models.CharField(db_column="gender",max_length=6, choices=requestChoice,help_text="") 
    mobileNo = models.CharField(db_column='mobileNo', max_length=16,help_text="")   
    dob = models.DateField(db_column="dob",help_text="") 
    major = models.CharField(db_column="major",max_length=255,help_text="") 
    graduationYear = models.IntegerField(db_column='graduationYear',max_length=11,help_text="")   
    canAddNews = models.BooleanField(db_column='canAddNews',default=False,help_text="")   
    receivePrivateMsgNotification = models.BooleanField(db_column='receivePrivateMsgNotification',default=True ,help_text="")   
    receivePrivateMsg = models.BooleanField(db_column='receivePrivateMsg',default=True ,help_text="") 
    receiveCommentNotification = models.BooleanField(db_column='receiveCommentNotification',default=True ,help_text="")   
    receiveLikeNotification = models.BooleanField(db_column='receiveLikeNotification',default=True ,help_text="")   
    receiveFavoriteFollowNotification = models.BooleanField(db_column='receiveFavoriteFollowNotification',default=True ,help_text="")   
    receiveNewPostNotification = models.BooleanField(db_column='receiveNewPostNotification',default=True ,help_text="")   
    allowInPopularList = models.BooleanField(db_column='allowInPopularList',default=True ,help_text="")   
    xmppResponse = models.TextField(db_column='xmppResponse',help_text="")   
    xmppDatetime = models.DateTimeField(db_column='xmppDatetime', help_text="")   
    status = models.BooleanField(db_column="status", default=False, help_text="") 
    deactivatedByAdmin = models.BooleanField(db_column="deactivatedByAdmin", default=False, help_text="") 
    createdAt = models.DateTimeField(db_column='createdAt', auto_now=True, help_text="")   
    modifiedAt = models.DateTimeField(db_column='modifiedAt', auto_now=True, help_text="")   
    updatedBy = models.ForeignKey(User,db_column="updatedBy",help_text="Logged in user updated by ......") 
    lastPasswordReset = models.DateTimeField(db_column='lastPasswordReset',help_text="") 
    authorities = models.CharField(db_column="departmentId",max_length=255,help_text="") 
 
    class Meta: 
        managed = False 
        db_table = 'users' 

我正在使用的查询正在生成所需的输出，但 sloq 是:-

universities = Universities.objects.using('cms').all() 
    for item in universities: 
        studentcount = Users.objects.using('cms').filter(universityId=item.id,userTypeId=2).count() 
        facultyCount = Users.objects.using('cms').filter(universityId=item.id,userTypeId=1).count() 
        clubCount = Users.objects.using('cms').filter(universityId=item.id,userTypeId=3).count() 
        totalcount = Users.objects.using('cms').filter(universityId=item.id).count() 
        print studentcount,facultyCount,clubCount,totalcount 
        print item.name 

请您参考如下方法:

您应该使用注释来获取每所大学的计数，并使用条件表达式来获取基于条件的计数 (docs)

Universities.objects.using('cms').annotate( 
    studentcount=Sum(Case(When(users_set__userTypeId=2, then=1), output_field=IntegerField())), 
    facultyCount =Sum(Case(When(users_set__userTypeId=1, then=1), output_field=IntegerField())), 
    clubCount=Sum(Case(When(users_set__userTypeId=3, then=1), output_field=IntegerField())), 
    totalcount=Count('users_set'), 
)