A search feature that filters products by a meta value had been fine for months, then suddenly wasn't. Nothing in the code had changed, only the amount of data sitting behind it.
We ran EXPLAIN on the query the plugin was generating and saw it doing a full table scan across wp_postmeta, which by that point had passed a hundred thousand rows across all the site's products, options, and revision history. Every search request meant MySQL walking the entire table looking for rows matching both a specific meta_key and a specific meta_value, one row at a time, with no index to jump to.
Adding a composite index on meta_key and meta_value brought it back down to milliseconds. The change itself was a single ALTER TABLE statement, the annoying part was realizing it needed doing at all, since nothing in the plugin's code had changed between "instant" and "eight seconds."
Why it crept up slowly
The postmeta table is one of those places where WordPress stores a lot more than you'd expect just from looking at a site's front end, revision metadata, plugin settings tucked away as post meta instead of the options table, thumbnail sizing data, on top of whatever custom fields a theme or plugin actually uses for content. All of that competes for the same table and the same lack of indexing on values that aren't the primary key.
A hundred thousand rows sounds like a lot until you remember every product can easily have ten or fifteen meta rows attached to it just from normal theme and plugin behavior, so it doesn't take an enormous catalog to get there. We hadn't been watching table sizes at all, there was no dashboard or alert, just a client email saying the search page had gotten slow.
What we changed going forward
We started running EXPLAIN on any custom query a plugin adds before considering that feature finished, not just testing that it returns the right rows. A query that returns correct results in half a second on a test database with fifty rows can still be doing a full table scan, it just doesn't show up as a problem until the table is a hundred times bigger.
We also started seeding local development databases with a more realistic amount of dummy content before final testing on anything doing custom meta queries, rather than the handful of test posts most of us default to while building a feature. It's an extra step that feels like overkill on a Tuesday afternoon, and pays for itself the first time it catches something like this before a client ever notices.
Lesson we keep relearning: a query that feels fast in development with fifty test posts tells you nothing about how it behaves once a client has been adding content for a year. Worth testing against a realistic dataset size before launch, not after a client complains that their own search page has gotten slow on them.