Django模型 - SELECT DISTINCT（foo）FROM表太慢了 [英] Django Models - SELECT DISTINCT(foo) FROM table is too slow

问题描述

我有一个具有13M行的MySQL表。我可以直接查询db作为

  SELECT DISTINCT（refdate）FROM myTable 
  / pre> 
 
 
查询需要0.15秒，很棒。
 
 
 
将等效表定义为Django模型，查询为
  myTable.objects.values（`refdate`）.distinct（）
  
需要很长时间。是因为在 distinct（）之前列表中的项目太多。 
解决方案
谢谢@solarissmoke指向 connection.queries 。 
 
 
 
我期待看到
  SELECT DISTINCT refdate FROM myTable 
  
相反，我有
  SELECT DISTINCT refdate，itemIndex，itemType FROM myTable ORDER BY itemIndex，refdate，itemType。 
  
然后我看看在 models.py 。
  unique_together =（（'nodeIndex'，'refdate'，'nodeType'），）
 = ['nodeIndex'，'refdate'，'nodeType'] 
  
从与默认排序或order_by的交互 
 
 
通常你不会想要额外的列在结果中播放一部分，所以清除订购，或者至少确保它被限制
 
 
所以我试过 order_by（）来刷新以前定义的顺序和瞧瞧！
  myTable.objects.values（'refdate'）。 order_by（）。distinct（）
  
 
I have a MySQL table with 13M rows. I can query the db directly as
SELECT DISTINCT(refdate) FROM myTable
The query takes 0.15 seconds and is great.


The equivalent table defined as a Django model and queried as
myTable.objects.values(`refdate`).distinct()
takes a very long time. Is it because there are too many items in the list before distinct(). How do I do this in a manner that doesn't bring everything down?
解决方案
Thank you @solarissmoke for the pointer to connection.queries. 

I was expecting to see 
SELECT DISTINCT refdate FROM myTable
Instead, I got 
SELECT DISTINCT refdate, itemIndex, itemType FROM myTable ORDER BY itemIndex, refdate, itemType. 
I then looked at myTable defined in models.py.
unique_together = (('nodeIndex', 'refdate', 'nodeType'), )
ordering = ['nodeIndex', 'refdate', 'nodeType']
From Interaction with default ordering or order_by 

  
normally you won’t want extra columns playing a part in the result, so clear out the ordering, or at least make sure it’s restricted only to those fields you also select in a values() call.
So I tried order_by() to flush the previously defined ordering and voila!
myTable.objects.values('refdate').order_by().distinct()


                        
这篇关于Django模型 -  SELECT DISTINCT（foo）FROM表太慢了的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！