简单查询导致高数据库CPU使用率

Question

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?

Answer 1

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).