MySQL order_by 优化

Question

我们有一个复杂的查询，它使用跨表和 order_by 的联接。

示例如下:

select distinct `accounts`.`id`,
    `accounts`.`number_of_listings` as alias_0
from `accounts`
left outer join `revenue_item_account_leads` on `revenue_item_account_leads`.`account_id` = `accounts`.`id`
left outer join `matches` on `matches`.`matchable_id` = `accounts`.`id`
    and `matches`.`matchable_type` = 'Account'
where `accounts`.`locale_id` = 1
    and (
        revenue_item_account_leads.platform_id is null
        or (revenue_item_account_leads.platform_id != 6)
        )
    and (
        matches.matched_matchable_id is null
        or (
            matches.matched_matchable_id in (14, 31, 37)
            and matches.score < 0.75
            )
        or (matches.matched_matchable_id not in (14, 31, 37))
        )
    and (accounts.number_of_listings > 0)
order by `accounts`.`number_of_listings` desc LIMIT 25 OFFSET 0

不带 order_by 的查询将在 1 秒内完成。带 order_by 的查询会在 5 秒内完成(使其无法在生产中使用)。

accounts.number_of_listings 上已有索引。此外，还有一个关于我们加入的任何关联的索引。

关于如何改进这个有什么想法吗？

Answer 1

尝试以下查询

select distinct `accounts`.`id`, `accounts`.`number_of_listings` as alias_0
from `accounts`
left outer join `revenue_item_account_leads`
    on `revenue_item_account_leads`.`account_id` = `accounts`.`id`
    and revenue_item_account_leads.platform_id = 6
left outer join `matches`
    on  `matches`.`matchable_id` = `accounts`.`id`
    and `matches`.`matchable_type` = 'Account'
    and matches.matched_matchable_id in (14, 31, 37)
    and and matches.score >= 0.75
where `accounts`.`locale_id` = 1
    and accounts.number_of_listings > 0
    and revenue_item_account_leads.platform_id is null
    and matches.matched_matchable_id is null
order by `accounts`.`number_of_listings` desc LIMIT 25 OFFSET 0

这些索引:

accounts(locale_id, number_of_listings, id)
revenue_item_account_leads(account_id, platform_id)
matches(matchable_id, matchable_type, matched_matchable_id, score)

根据您的关系和数据，您甚至可能不需要 DISTINCT 关键字。