Query optimization and index suggestion needed

[Pandit P]

Hello,

I have a large table with 6 partitions applied on a varchar column called "group_by" and 6 subpartitions on the year column. Table contains almost 30 million records spread across 3 years.

There is a composite primary key (id, group_by, year).

Since there is no concept of global or local indexes in Mysql. We have added appropriate indexes on the table itself.


[ SELECT
channel AS channel_name,
concat_ws('-',index_code,index_label) AS partindexcode,
'' AS mon ,
'' AS subtotal,
ROUND(SUM(
CASE
WHEN YEAR = 2020
THEN gross_amount
ELSE 0
END)) AS salestotal,
IFNULL(ROUND(( (
CASE
WHEN YEAR = YEAR('2020-05-19')
THEN (((SUM(
CASE
WHEN YEAR = 2020
THEN gross_amount
ELSE 0
END) ) / (98)) * (261))
ELSE (SUM(
CASE
WHEN YEAR = 2020
THEN gross_amount
ELSE 0
END))
END)/(SUM(
CASE
WHEN YEAR = 2019
THEN gross_amount
ELSE 0
END))) * 100,0 ),0) AS projection,
channel_id AS channel_id ,
index_id AS partindex_id
FROM
sales
WHERE
groupby_type = 'CHANNEL_ID' AND
business_unit_id IN (2,
3,4,5,6,7,8,10,11,12,13,14) AND user_id IN (16, 17,18,19,20,21, 22, 210,212,220,234,250,252,2,3,4,5,6,7,8,9,10,11,12,13,14,15,23) AND
(year BETWEEN 2019 AND 2020)
GROUP BY
channel_id;
]


I have created a composite index on

(groupby_type, business_unit_id, user_id, year, channel_id, gross_amount)

But it is not helping me to execute the query faster. It is taking almost 1.5 minutes to produce the result.

I have used the index hint in the query (USE INDEX) since the query was not picking the index which I created for this query, instead it was using some other index which was available before.

After executing for 1.5 minutes it is giving 15 records output but taking lot of time.

My partition is on "Group_by" column and subpartition is on year. Shoul dI include them while creating index?


How can I optimize this query to produce the result faster?


Please advise.

Thanks in advance.

Regards
Pravin