Show current process in MySQL console

Ever wondered why your MySQL server is using 100% of your CPU resources ?

The MySQL show processlist instruction enables you to know which query is currently running on your server.

In this example, I use a demo database from Launchpad : https://launchpad.net/test-db (great database for your demos btw). I prepared an intentionally over-complicated query that is just here to take some time to execute (don't focus on the meaning of this query).

Here is the query :

SELECT * FROM employees
INNER JOIN dept_emp ON employees.emp_no = dept_emp.emp_no
INNER JOIN departments ON departments.dept_no = dept_emp.dept_no
INNER JOIN dept_manager ON dept_manager.dept_no = departments.dept_no
WHERE employees.emp_no IN (
    SELECT employees.emp_no
    FROM employees
    INNER JOIN dept_emp ON employees.emp_no = dept_emp.emp_no
    INNER JOIN departments ON departments.dept_no = dept_emp.dept_no
    INNER JOIN dept_manager ON dept_manager.dept_no = departments.dept_no
    WHERE dept_manager.from_date > '1990-01-01' AND dept_manager.to_date < '1991-01-01'
);

Execute this query in (it takes 16 seconds to execute on my laptop), and check which query is currently handled by your server :

$ mysql -u username -ppwd
$ mysql> show full processlist\G;

This should give you something like this (don't forget the full option, if you want the full query to be echoed) :

*************************** 1. row ***************************
Id: 67
User: username
Host: localhost:59331
db: NULL
Command: Query
Time: 0
State: NULL
Info: show full processlist
*************************** 2. row ***************************
Id: 74
User: username
Host: localhost:59480
db: employees
Command: Query
Time: 15
State: Sending data
Info: SELECT * FROM employees
    INNER JOIN ... here goes the rest of the echoed query
2 rows in set (0.00 sec)

ERROR:
No query specified

This command always shows a result for the show processlist query itself, so don't care about the first result.
The second one tells us that, when we checked the process list, the server was sending the data for 15 seconds (out of 16 seconds).

In this case, I should probably add a where clause to reduce the size of the result.