mysql - 改进糟糕的 annotate() 查询

Question

这是我当前的查询，它按最近的时间戳订购 order_item。

order_items.annotate(newest_note_time=Max('ordernotes__timestamp')).
    order_by('newest_note_time')

它有效。但是，在 debug-toolbar 中查看它时，它给了我两个残酷的查询，它们几乎完全相同。我试过这样做:

order_items = order_items.order_by('-ordernotes__timestamp')

但这会导致不正确的查询，给我重复的结果。

有没有更好的方法来执行此查询而无需在此处跳转到原始 SQL？

这是其中一个查询(第二个基本相同，不知道为什么会生成第二个...)

SELECT ••• FROM `order_orderitem`
INNER JOIN `order_orderitemstatus` ON (`order_orderitem`.`status_id` = `order_orderitemstatus`.`id`)
INNER JOIN `order_order` ON (`order_orderitem`.`order_id` = `order_order`.`id`)
INNER JOIN `title_title` ON (`order_orderitem`.`title_id` = `title_title`.`id`)
INNER JOIN `home_service` ON (`order_orderitem`.`service_id` = `home_service`.`id`)
LEFT OUTER JOIN `order_ordernotes` ON (`order_orderitem`.`id` = `order_ordernotes`.`order_item_id`)
WHERE NOT (`order_orderitemstatus`.`name` IN ('Complete', 'Live', 'Archived'))
GROUP BY
  `order_orderitem`.`id`, `order_orderitem`.`order_id`, `order_orderitem`.`title_id`, `order_orderitem`.`service_id`,
  `order_orderitem`.`metadata_locale_id`, `order_orderitem`.`purchase_order`, `order_orderitem`.`due_date`, `order_orderitem`.`feature`,
  `order_orderitem`.`trailer`, `order_orderitem`.`artwork`, `order_orderitem`.`chaptering`, `order_orderitem`.`cc`,
  `order_orderitem`.`metadata`, `order_orderitem`.`subtitles`, `order_orderitem`.`forced_narrative`, `order_orderitem`.`qc_note`,
  `order_orderitem`.`audio`, `order_orderitem`.`dub_card`, `order_orderitem`.`live_url`, `order_orderitem`.`metadata_valid`,
  `order_orderitem`.`status_id`, `order_orderitem`.`date_created`, `order_order`.`id`, `order_order`.`number`, `order_order`.`provider_id`,
  `order_order`.`date_created`, `order_order`.`date_ordered`, `order_order`.`is_archived`, `title_title`.`id`, `title_title`.`film_id`,
  `title_title`.`name`, `title_title`.`provider_id`, `title_title`.`original_locale_id`, `title_title`.`country_of_origin_id`,
  `title_title`.`synopsis`, `title_title`.`production_company`, `title_title`.`copyright`, `title_title`.`run_time`,
  `title_title`.`original_theatrical_release`, `title_title`.`color`, `title_title`.`film_type`, `title_title`.`no_cc_reason`,
  `title_title`.`includes_hd`, `title_title`.`provider_identifier`, `title_title`.`episode_production_number`, `title_title`.`container_position`,
  `title_title`.`season_id`, `home_service`.`id`, `home_service`.`name`, `home_service`.`notes`, `order_orderitemstatus`.`id`,
  `order_orderitemstatus`.`name`, `order_orderitemstatus`.`department_id`, `order_orderitemstatus`.`is_finished`,
  `order_orderitemstatus`.`ordering` ORDER BY NULL

Answer 1

.select_related(depth=1)

将其添加到原始查询的末尾，看看是否发生了任何奇迹。

对于糟糕的查询，另一种可能的修复方法是只缓存页面。将以下内容添加到文件顶部:

from django.views.decorators.cache import cache_page

然后就在你的 def 之上...添加:

@cache_page(60 * 5)

即 60 秒 * 5，持续 5 分钟。将时间更改为适合您的时间。