EverSQL Database Performance Knowledge Base

The optimization process and recommendations:

1. Avoid Calling Functions With Indexed Columns (query line: 36): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index. For example, if the column `fin_week` is indexed, the index won’t be used as it’s wrapped with the function `coalesce`. If you can’t find an alternative condition that won’t use a function call, a possible solution is to store the required value in a new indexed column.
2. Avoid Calling Functions With Indexed Columns (query line: 37): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index. For example, if the column `cost_centre` is indexed, the index won’t be used as it’s wrapped with the function `coalesce`. If you can’t find an alternative condition that won’t use a function call, a possible solution is to store the required value in a new indexed column.
3. Avoid Calling Functions With Indexed Columns (query line: 37): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index. For example, if the column `cost_Centre` is indexed, the index won’t be used as it’s wrapped with the function `coalesce`. If you can’t find an alternative condition that won’t use a function call, a possible solution is to store the required value in a new indexed column.
4. Avoid Calling Functions With Indexed Columns (query line: 41): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index. For example, if the column `fin_week` is indexed, the index won’t be used as it’s wrapped with the function `coalesce`. If you can’t find an alternative condition that won’t use a function call, a possible solution is to store the required value in a new indexed column.
5. Avoid Calling Functions With Indexed Columns (query line: 43): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index. For example, if the column `cost_centre` is indexed, the index won’t be used as it’s wrapped with the function `coalesce`. If you can’t find an alternative condition that won’t use a function call, a possible solution is to store the required value in a new indexed column.
6. Avoid Calling Functions With Indexed Columns (query line: 43): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index. For example, if the column `cost_Centre` is indexed, the index won’t be used as it’s wrapped with the function `coalesce`. If you can’t find an alternative condition that won’t use a function call, a possible solution is to store the required value in a new indexed column.
7. Avoid Calling Functions With Indexed Columns (query line: 45): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index. For example, if the column `PG_Code` is indexed, the index won’t be used as it’s wrapped with the function `coalesce`. If you can’t find an alternative condition that won’t use a function call, a possible solution is to store the required value in a new indexed column.
8. Avoid Subqueries (query line: 7): We advise against using subqueries as they are not optimized well by the optimizer. Therefore, it's recommended to join a newly created temporary table that holds the data, which also includes the relevant search index.
9. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
10. Explicitly ORDER BY After GROUP BY (modified query below): By default, the database sorts all 'GROUP BY col1, col2, ...' queries as if you specified 'ORDER BY col1, col2, ...' in the query as well. If a query includes a GROUP BY clause but you want to avoid the overhead of sorting the result, you can suppress sorting by specifying 'ORDER BY NULL'.

Optimal indexes for this query:

ALTER TABLE `Spaceblock_Name_to_PG` ADD INDEX `spaceblock_to_idx_spaceblockname_pg_code` (`SpaceblockName_SID`,`PG_Code`);
ALTER TABLE `sales_pg_wk` ADD INDEX `sales_wk_idx_pg_code` (`pg_code`);

The optimized query:

SELECT
        Cost_centre,
        Fin_week,
        stage_1.SpaceblockName_SID,
        sum(Propor_rep_SRV) AS Total_SpaceblockName_SID_SRV 
    FROM
        (SELECT
            coalesce(space_side.fin_week,
            sales_side.fin_week) AS Fin_week,
            coalesce(space_side.cost_centre,
            sales_side.cost_Centre) AS Cost_centre,
            space_side.SpaceblockName_SID,
            CASE 
                WHEN space_side.SpaceblockName_SID IS NULL THEN sales_side.SalesExVAT 
                ELSE sum(space_side.TLM) / nullif(sum(sum(space_side.TLM)) OVER (PARTITION 
            BY
                coalesce(space_side.fin_week,
                sales_side.fin_week),
                coalesce(space_side.cost_centre,
                sales_side.cost_Centre),
                coalesce(Spaceblock_Name_to_PG.PG_Code,
                sales_side.PG_Code) ),
                0) * sales_side.SalesExVAT END AS Propor_rep_SRV 
FROM
WeeklySpace AS space_side 
INNER JOIN
Spaceblock_Name_to_PG 
    ON space_side.SpaceblockName_SID = Spaceblock_Name_to_PG.SpaceblockName_SID 
    AND Spaceblock_Name_to_PG.PG_Code < 10000 FULL 
OUTER JOIN
sales_pg_wk AS sales_side 
    ON space_side.fin_week = sales_side.fin_week 
    AND space_side.Cost_Centre = sales_side.Cost_Centre 
    AND Spaceblock_Name_to_PG.PG_code = sales_side.pg_code 
WHERE
coalesce(space_side.fin_week, sales_side.fin_week) BETWEEN 201538 AND 201550 
AND coalesce(space_side.cost_centre, sales_side.cost_Centre) IN (
    3, 2800
) 
GROUP BY
coalesce(space_side.fin_week,
sales_side.fin_week),
coalesce(space_side.cost_centre,
sales_side.cost_Centre),
coalesce(Spaceblock_Name_to_PG.PG_Code,
sales_side.PG_Code),
sales_side.SalesExVAT,
space_side.SpaceblockName_SID 
ORDER BY
NULL) AS stage_1 
GROUP BY
Cost_centre,
Fin_week,
stage_1.SpaceblockName_SID 
ORDER BY
NULL