I have the following simple MySQL query:
SELECT COUNT(*) FROM images WHERE path LIKE '%/path/to/image.jpg'
The field 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?
The field path has an index.
The index is useless for such a query. Btree indexes can only be used with LIKE when the LIKE argument contains variable postfix (LIKE 'something%'). They are not used with variable prefixes (LIKE '%something'). You can check the EXPLAIN your_query_here, there you will see that the query uses full table scan.
So the answer is, your MySQL eats CPU searching through the table (it's likely the table fits entirely in memory, so it's still pretty fast).
This kind of query is not simple for server at all. In your case it is performed fast exactly because it utilizes full available CPU time.
Consider splitting the 'path' field to path and filename separately, it would allow indexing the filename and performing search by it without having variable postfixes or prefixes, in hope that it will reduce the number of records that the search by path will be performed on.
You should use LOCATE instead:
... WHERE LOCATE('/path/to/image.jpg', path)
This will use your index where LIKE '%...' will not.