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

P粉610028841 · Answer

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