MySQL CPU pegged on specific query
I wouldn't be too worried about MySQL using 100% CPU, especially on a multi-core VPS. But a good way to track down things like this is to enable the MySQL slow query log

You can set whatever thresholds you want, and then peruse the log at your convenience to see the queries that are actually slowing things down.