Hidden features of MySQL

An often unused but more verbose

SHOW FULL PROCESSLIST

is handy, but not as good as the awesome non-enterprise query analyser - enable like so

    mysql> set profiling=1;
    Query OK, 0 rows affected (0.00 sec)

These two are junk queries to fill the profiles table,

        
    mysql> select * from _test.customers;
    ERROR 1146 (42S02): Table '_test.customers' doesn't exist

    mysql> select * from test.customers limit 0;
    Empty set (0.00 sec)
    

Get a list of all queries profiled and their duration

        
    mysql> show profiles;
    +----------+------------+-------------------------------+
    | Query_ID | Duration   | Query                         |
    +----------+------------+-------------------------------+
    |        1 | 0.00013400 | select * from _test.customers |
    |        2 | 0.01546500 | select * from test.customers  |
    +----------+------------+-------------------------------+

Display info for last query would just be "show profile" - or you can specify a query

  
    mysql> show profile for query 2;
    +----------------------+----------+
    | Status               | Duration |
    +----------------------+----------+
    | starting             | 0.000053 |
    | checking permissions | 0.000007 |
    | Opening tables       | 0.000014 |
    | System lock          | 0.000006 |
    | Table lock           | 0.000008 |
    | init                 | 0.000065 |
    | optimizing           | 0.000003 |
    | executing            | 0.000201 |
    | end                  | 0.000003 |
    | query end            | 0.000002 |
    | freeing items        | 0.000020 |
    | logging slow query   | 0.000002 |
    | cleaning up          | 0.000004 |
    +----------------------+----------+
    13 rows in set (0.00 sec)

You can also request specific info such as CPU, BLOCK IO and SWAPS amongst others (all on man page)

  
    mysql> show profile cpu for query 2;
    +----------------------+----------+----------+------------+
    | Status               | Duration | CPU_user | CPU_system |
    +----------------------+----------+----------+------------+
    | starting             | 0.000056 | 0.001000 |   0.000000 |
    | checking permissions | 0.000007 | 0.000000 |   0.000000 |
    | Opening tables       | 0.000010 | 0.000000 |   0.000000 |
    | System lock          | 0.000005 | 0.000000 |   0.000000 |
    | Table lock           | 0.000007 | 0.000000 |   0.000000 |
    | init                 | 0.000059 | 0.000000 |   0.000000 |
    | optimizing           | 0.000003 | 0.000000 |   0.000000 |
    | statistics           | 0.015022 | 0.000000 |   0.000000 |
    | preparing            | 0.000014 | 0.001000 |   0.000000 |
    | executing            | 0.000004 | 0.000000 |   0.000000 |
    | Sending data         | 0.000245 | 0.000000 |   0.000000 |
    | end                  | 0.000004 | 0.000000 |   0.000000 |
    | query end            | 0.000002 | 0.000000 |   0.000000 |
    | freeing items        | 0.000021 | 0.000000 |   0.000000 |
    | logging slow query   | 0.000002 | 0.000000 |   0.000000 |
    | cleaning up          | 0.000004 | 0.000000 |   0.000000 |
    +----------------------+----------+----------+------------+
    16 rows in set (0.00 sec)

Don't forget to disable it afterwards, as the logging adds overhead.

  
    mysql> set profiling=0;
    Query OK, 0 rows affected (0.00 sec)

Some MySQL commands that aren't always commonly known or remembered.

Change result set orientation to vertical for easy reading and pasting.

mysql> SELECT CURDATE(), CURTIME()\G
*************************** 1. row ***************************
CURDATE(): 2009-06-26
CURTIME(): 12:10:37

Cancel the query you are currently typing while leaving it in your history.

mysql> SELECT CURDATE(), CURTIME()\c
mysql>

Edit a query or last query (respectively) with your favourite $EDITOR.

mysql> SELECT CURDATE(), CURTIME()\e
mysql> \e

Clear the output of the console.

mysql> \! clear

Compare result sets by MD5 hash.

mysql> pager md5sum -
PAGER set to 'md5sum -'
mysql> SELECT CURDATE(), CURTIME();
d24e22e4e2d33dfda9f01ba934b7676a  -
mysql> nopager
PAGER set to stdout

Change your prompt.

mysql> prompt (\u@\h) [\d]>\_
PROMPT set to '(\u@\h) [\d]>\_'
(dan@localhost) [test]>

Search your command history for a given string (like Bash).
Start typing a search term and repeat ^R to cycle through the results.

^R
(reverse-i-search)`DATE': SELECT CURDATE(), CURTIME();