mysql - MySQL-define index for select query which has (inner join, CTE and partitions)

Question

我的数据库中有 2 个表：subscriptions和companies. 应该运行以下查询：

WITH ranked_rows
AS
    (
        SELECT
            s.*
            , ROW_NUMBER() OVER (PARTITION BY user_id, company_id ORDER BY insert_timestamp DESC) AS rn
        FROM
            subscriptions AS s
        WHERE
            token = 300
            AND expire_timestamp>=... 
            AND expire_timestamp<...

    )
SELECT
    rr.*
    , c.name
    , c.activity_plan
FROM
    ranked_rows          AS rr
    INNER JOIN comapnies AS c
        ON rr.company_id = c.id
WHERE
    rr.rn = 1
    AND c.activity_plan = 1
    AND c.is_active = true
ORDER BY
    c.insert_timestamp;

我定义了以下索引：

subscriptions table:
(token, expire_timestamp)
(insert_timestamp)
(company_id)

companies table:
(id)
(insert_timestamp)
(activity_plan, is_active)

我的问题是这些索引是否足够并针对此查询进行了优化？我应该将另一个复合索引定义为(activity_plan, is_active, insert_timestamp)？如果查询的最后一行是ORDER BY rr.insert_timestampinstedORDER BY c.insert_timestamp怎么办？

标签： mysqlsqlindexingmariadb