Query causing huge CPU utilization and high latency

Question

The following query is causing huge CPU utilization and high latency in my database environment. I tried using different types of indexes to improve query performance, but unfortunately any index did not help improve performance. Is there any suggestion to rewrite the query to get the same result.

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);

Answer 1

This index is good for view_mats: INDEX(mngy, nonUnixjdjf). That is, for trip_dsty: INDEX(mngy, jdjf).

And (possibly) delete idx_n1, since it only contains the beginning of that index.

(I'm not completely versed in column stores; the advice above is for InnoDB indexes; it may apply here.)

In general, (m,n) on Float and Double is useless and may cause rounding errors. double(20,10) should probably be the plain double or DECIMAL(20,10).

LIMIT 10000 - Did you really deliver that many rows to the client? Shoveling so much is also a performance issue.