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();

Tricks I've learnt that may be of use to some:

To run the file you've previously saved:

source filename      # Alternatively you can enter "\\. filename".

Use "\!" to access shell commands. For example:

\\! ls c*sql   # To list all your SQL files in directory starting with "c".

So if you wanted to write your statement to a file (without using the editor option) you could enter:

\\! echo 'select * from emp where job ="salesman" '   > test2.sql   # Editor option seems easier to me though!

If you enter

\\T filename

you will then have your statements and query results directed/printed to the filemame you specified. Use \\t to turn this off.

Terminate a query with \\G instead of a ";" in order to show output in a row format instead of in columns.

Don't rule out using a Where...LIKE clause with the SHOW statement. For example:

SHOW STATUS LIKE '%cache%';

Lastly, to find the location of your MySQL data directory without looking at the my.cnf file use:

SHOW VARIABLES LIKE 'datadir';

I personally like the SHOW command

You could do
SHOW PROCESSLIST - To see all the running connections to mysql
SHOW CREATE TABLE TableName - To see the sql used to create the table
SHOW CREATE PROCEDURE ProcedureName - To see the sql used to create the SP
SHOW VARIABLES - To see all the system variables

Get the full list here

Actually documented, but very annoying: automatic date conversions for incorrect data.

Before MySQL 5.0.2, MySQL is forgiving of illegal or improper data values and coerces them to legal values for data entry. In MySQL 5.0.2 and up, that remains the default behavior, but you can change the server SQL mode to select more traditional treatment of bad values such that the server rejects them and aborts the statement in which they occur.

Sometimes you'll be "lucky" when MySQL doesn't adjust the input to nearby valid dates, but instead stores it as 0000-00-00 which by definition is invalid. However, even then you might have wanted MySQL to fail rather than silently storing this value for you.

Another feature that sets MySQL apart from other databases is the REPLACE INTO command. You can do:

REPLACE INTO T1 (Col1, Col2 )
SELECT Col1, Col2 FROM T2;

You can also write the replace statement just like you write the update statement:

REPLACE INTO T1 
SET Col1 = 'SomeValue1'
, Col2   = 'SomeValue2'

Not really a hidden feature, but it's lesser known and I use it a lot to save doing a query to check if something exists before doing an UPDATE or INSERT

INSERT ... ON DUPLICATE KEY UPDATE

Documentation is here

To see the query execution plan use EXPLAIN

e.g.

EXPLAIN 
SELECT T1.Col1, T2.Col2
FROM T1 INNER JOIN T2 ON T1.Id = T2.Id
WHERE T1.Col3 = 10

Not really hidden, but slow-query-log can be really helpful in tracking down cause of performance problems at peak times.

In file my.cnf, [mysqld] section - add:

log_slow_queries=/var/log/mysql/mysql-slow.log
# log queries that took more than 1 sec
long_query_time = 1

Well, can't mark this as a duplicate since it's a different site (and I don't have the rep here) but I'll just link to this excellent stackoverflow post for the same question: