For your convenience, a short phrase book, starting with explanation of process states where MySQL is mostly working to look up data from tables:

• “Sending data” – reading data from tables (or looking it up)
• “Copying to tmp table” – reading data from tables (or looking it up)
• “Copying to tmp table on disk” – query needs a rewrite
• “statistics” – looking up data from tables
• “Sorting result” – reading data from tables (or looking it up)

Locking is fun:

• “update” – There is big chance that it is waiting for a lock, can be updating as well, on busy systems
• “Locked” – Even bigger chance that it is MyISAM table waiting to be converted to InnoDB

Table opening is even funnier:

• “Opening tables” – some other idiot thread is holding the LOCK_open or similar mutex
• “Closing tables” – some other idiot thread is holding the LOCK_open or similar mutex
• “Opening table” – the idiot thread (or not that smart DBA who thinks that table_cache should be set to current value)
• “Flushing tables” – someone decided that flushing tables is good idea, but forgot to kill long running queries
• “Waiting for tables” – same as “Flushing tables”

If you have replication:

• “Connecting to server” – could not connect to server, waiting to retry
• “Reconnecting after a failed master event read” – master and slave have same @server_id
• “Registering slave on master” – master and slave have same @server_id
• “Waiting to reconnect after a failed master event read” – master and slave have same @server_id

Few more tricky ones:

• “Sleep” – last millisecond it was probably not sleeping, due to how process scheduling and kernel level buffering works probably it has finished right before kernel switched to the ‘SHOW PROCESSLIST’ thread.
• “Writing to net” – usually, not writing to net (client has blocked) – actual writing to net is usually nearly non-blocking operation, as long as client doesn’t sleep.
• “Killed” – actually, someone tried to kill it, but it doesn’t care, as it doesn’t check for thd->killed flag

And in the user column:

• “unauthenticated user” – are you sure your DNS is working right?
• “system user” – it isn’t user, these are (hopefully plural) replication threads, that don’t have any “user” credentials
• “root” – either that is you running the PROCESSLIST, another session you forgot you are running or you forgot to create separate user for your application

I probably didn’t write quite a few important ones, but some of them are self-evident (such as “init” and “end”), and others probably will never show up :)