在优化查询方面需要帮助

Question

实际上我正在使用下面的查询来获取结果，但我觉得这是一个有点冗长的查询，我可以在优化下面的查询时得到帮助吗？

SELECT
    o.orderid AS id,
    sd.datestart AS startdate,
    sd.dateend AS enddate,
    o.comment AS comment,
    o.productname AS name,
    o.suspended AS sus,
    o.voided AS void,
    o.invoice_number AS inumber,
    GROUP_CONCAT(js.title) AS sections,
    GROUP_CONCAT(js.sectionid) AS sectionid,
    js.is_science_education AS isse,
    o.amount AS amount,
    js.isbundle,
    o.addedbyuserid,
    (SELECT
            COUNT(*)
        FROM
            institutiontoorder AS ito2
        WHERE
            orderid = ito.orderid) AS multiple
FROM
    institutiontoorder AS ito
        LEFT JOIN
    subscribedates AS sd ON ito.orderid = sd.orderid
        LEFT JOIN
    ordertosection AS ots ON ots.orderid = ito.orderid
        LEFT JOIN
    orders AS o ON o.orderid = ito.orderid
        LEFT JOIN
    institutions AS i ON i.institutionid = ito.institutionid
        JOIN
    journalsections AS js ON js.sectionid = ots.sectionid
WHERE
    ito.institutionid = '1419'
        AND o.voided != 1
        AND o.suspended != 1
        AND (dateend >= NOW())
GROUP BY o.orderid
ORDER BY o.orderid DESC;

我正在使用多个joins，可以优化依赖子查询吗？

Answer 1

对查询的细微调整，如select count()到其自己的查询前/聚合，但仅针对所讨论的一个机构。当您将一些左连接添加到WHERE子句中时，它们实际上变成了内连接。

实际上，我将和部分移动到了所连接到的表的相应连接位置。

我将确保以下索引

table               index
InstitutionToOrder  (institutionid, orderid)
orders              (orderid, voided, suspended)
subscribeDates      (orderid, dateEnd )
OrderToSection      (orderid, sectionid )

而且，你也不需要到处使用" as“，比如列作为resultColumn或者表作为别名。它隐含着"AS“，项目后面只有一个空格。

SELECT
        o.orderid id,
        sd.datestart startdate,
        sd.dateend enddate,
        o.comment,
        o.productname name,
        o.suspended sus,
        o.voided void,
        o.invoice_number inumber,
        GROUP_CONCAT(js.title) sections,
        GROUP_CONCAT(js.sectionid) sectionid,
        js.is_science_education isse,
        o.amount,
        js.isbundle,
        o.addedbyuserid,
        preCount.multiple
    FROM
        institutiontoorder ito
            JOIN orders o 
                ON ito.orderid = o.orderid
                AND o.voided = 0
                AND o.suspended = 0
            JOIN subscribedates sd 
                ON ito.orderid = sd.orderid
                AND sd.dateend >= NOW()
            JOIN
                -- pre-aggregate counts once for just the 
                -- institution in question and join to it
                ( SELECT
                        ito2.orderid,
                        COUNT(*) muliple
                    FROM
                        institutiontoorder ito2
                    WHERE
                        ito2.institutionid = '1419'
                    group by
                        ito2.orderid ) AS PreCount
                on ito.orderid = PreCount.orderid
            LEFT JOIN ordertosection ots 
                ON ito.orderid = ots.orderid
                JOIN journalsections js 
                    ON ots.sectionid = js.sectionid
            LEFT JOIN institutions i 
                ON ito.institutionid = i.institutionid
    WHERE
        ito.institutionid = '1419'
    GROUP BY
        -- via Transitive Property, since o.orderid = ito.orderid,
        -- use the primary table in the query's orderID for group/order by
        ito.orderid
    ORDER BY 
        ito.orderid DESC;

根据O. Jones的评论，我完全同意对您的订单的无效和挂起的过滤器进行不平等测试。这些值有哪些选项...只有0或1，其中1表示无效或暂停？或者他们有其他的价值。我已调整为等于=0(表示未作废且未暂停)

Answer 2

o:    (orderid, dateend)
ito:  (institutionid, orderid)
ots:  (orderid, sectionid)
js:   (sectionid, isbundle, is_science_education, title)
i:    (institutionid)
sd:   (orderid, dateend, datestart)

列的顺序通常很重要。如果这些列中的任何一列太大(例如，如果title为TEXT)，则将其关闭。否则，这些索引中的大多数都是“覆盖”索引，从而提供一点额外的性能提升。

对于处理DRapp的ito2，ito列的顺序很重要