Query causing huge CPU utilization and high latency
P粉182218860
P粉182218860 2024-01-10 17:21:24
0
1
358

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粉182218860
P粉182218860

reply all(1)
P粉610028841

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.

Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
About us Disclaimer Sitemap
php.cn:Public welfare online PHP training,Help PHP learners grow quickly!