MySQL: Boiling JFrogs | Die wunderbare Welt von Isotopp
Posted by jpluimers on 2025/01/23
I love how Kristian Köhntopp often turns series of valuable tweets in a blog post. [Wayback/Archive] MySQL: Boiling JFrogs | Die wunderbare Welt von Isotopp is no different and has much more than the few quotes below (especially about the process of finding the solutions):
A work problem: A commercial application, Artifactory, where we do not control the source or the schema has performance problems involving a certain long running query.The data size and row counts are not outrageous, and the query itself and the schema are not broken. But the data is very skewed and for certain values the query is very slow, as almost the entire table is selected.We introduce an experimental covering index, and show a 16x improvement, going from 143s to 9s execution time. We advise the customer to ask for this covering index to be added officially using the normal way through MySQL portal.
…
This technique of using covering indexes I have learned from an Ex-MySQL colleagie, Domas Mituzas , who used it excessively to make Wikipedia fast while working as a Wikimedia DBA. Thanks, Domas.
…
But it matters not, we have functional indexes, so instead of indexing
nodes.node_namewe just indexlower(nodes.node_name)instead. That changes nothing for MySQL at all, but the optimizer requires this data duplication in order to see the possibility.
I’ve seen and used the covering indexes and functional indexes in other database environments as well: they form the base of valuable query optimisation techniques.
Original thread at [Wayback/Archive] Thread by @isotopp on Thread Reader App generated from first tweet [Wayback/Archive] Kris on Twitter: “Boiling JFrogs (“Why is Artifactory slow”) Yesterday, an internal team sounded the alarm, because their MySQL primary is sitting at 80+% CPU and I/O util. Normally, I’d point them to Solarwinds DPM and wish them good luck, but we did instead a deep dive into Artifactory.”
You can send pull requests as the source is at [Wayback/Archive] isotopp.github.io/2022-08-25-mysql-boiling-jfrogs.md at main · isotopp/isotopp.github.io.
--jeroen
The Wiert Corner - irregular stream of stuff 
Leave a comment