How to view the current queries running in a MYSQL database

The SHOW PROCESSLIST mysql command will show the live queries in your database. This can be quite useful if you are troubleshooting a slow web application or if you are getting the "too many connections" error message and suspect that it is caused some inefficient query to the database.

The output from SHOW PROCESSLIST looks something like this:


| Id | User | Host | db | Command | Time | State | Info                                                                             | 43930645 | username | host:44495 | dbname | Query | 1906 | Locked | DELETE FROM Cache WHERE id LIKE '/file/%-34333/' |
| 43933139 | username | host:50412 | dbname | Query | 3041 | updating | DELETE FROM Cache WHERE id LIKE '/file/%-72655/' |

In most database applications that I've built queries finishes within a second. And in this case there are two queries that have been running for 1906 and 3041 seconds, which clearly indicates that something is wrong.

In my particular case the WHERE LIKE queries had to touch a large number of rows even though the id column was indexed. This happened because the majority of items in the Cache table began with "/file/". I reduced the number of rows the database had to take into account by storing the ids in reverse order and reversing the LIKE query.