query causing huge cpu utilization and high latency

Question

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

WHklnE? Did this get hit with some bit-flip errors?

tadman
– tadman

2022-10-19 11:09:28 +00:00
Commented Oct 19, 2022 at 11:09 — tadman
– tadman, Commented Oct 19, 2022 at 11:09
sorry its type error , it is where clause, modified.

sportyrdy
– sportyrdy

2022-10-19 11:33:07 +00:00
Commented Oct 19, 2022 at 11:33 — sportyrdy
– sportyrdy, Commented Oct 19, 2022 at 11:33
There are 6 other typos involving "kln".

Rick James
– Rick James

2022-10-20 04:26:43 +00:00
Commented Oct 20, 2022 at 4:26 — Rick James
– Rick James, Commented Oct 20, 2022 at 4:26

Rick James · Accepted Answer · 2022-10-21 02:43:48Z

1

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.

edited Oct 21, 2022 at 2:43

answered Oct 20, 2022 at 4:24

Rick James

144k15 gold badges144 silver badges254 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

query causing huge cpu utilization and high latency

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related