I have the following simple MySQL query:
SELECT COUNT(*) FROM images WHERE path LIKE '%/path/to/image.jpg'
path has an index. Table has about 500,000 records.
Although the query takes less than a millisecond to execute, I am watching the CPU usage for MySQL ratchet higher and higher in the process list even though this is the only query. Eventually it climbs to 99%.
The only other thing the script does is increment a simple counter and then delete a file if the corresponding entry doesn't exist in the database.
The PHP code itself only uses a tiny bit of the CPU (0.3%) according to profiling.
I tried dropping it down to only do the first 5,000, but the script never finishes. I just see a blank page despite buffer flushes that are supposed to show me the progress every 25 records.
What could cause this sort of MySQL CPU usage increase over the same sort of super simple query?