The admin order search used LIKE with a leading wildcard. A B-tree index cannot help with that, so every search read the whole table, and the search-as-you-type box multiplied it. We compared a trigram index and full-text search on the same data and shipped both for different fields.
A 'random tip of the day' widget used ORDER BY RAND() LIMIT 1. It was fine at 5,000 rows and pinned the primary at 100 percent CPU at 1.4 million. Here is how we proved it and three random-row strategies that use an index instead.
When MySQL sits at 100% CPU, the guilty query is rarely the slowest one; it is usually a fast query run millions of times. A practical sequence for finding it: the processlist for right now, performance_schema statement digests for the last hours, the sys schema for shortcuts, and pt-query-digest for slow log history.