following query is causing huge cpu utilization and high latency on my db environment. i have tried to improve the query performance with different type of indexes but unfortuanately any indexes not helped to improve the performance. is there any suggessions to rewrite the query to get the same results.
query
SELECT kln.qsw, kln.mngy
FROM (
SELECT kln2.mngy, MAX(kln2.nonUnixjdjf) dm_hj
FROM mfh.view_mats kln2
WHERE kln2.jdjf <= '2022-10-19 10:47:25.000000'
GROUP BY kln2.mngy
) pun_ghky
JOIN pun_ghky.mngy = kln.mngy);
This index will be beneficial for view_mats: INDEX(mngy, nonUnixjdjf). That is, for trip_dsty: INDEX(mngy, jdjf).
And (probably) drop idx_n1 since it contains just the start of this index.
(I am not fully versed in Columnstore; the advice above is aimed at InnoDB indexing; it may apply here.)
In general, the (m,n) on Float and Double is useless and can lead to rounding errors. double(20,10) should probably be either plain double or DECIMAL(20,10).
LIMIT 10000 -- are you really delivering that many rows to the client? Shoveling that much is a performance issue, too.