Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

locked by voretaq7 Feb 27 '12 at 22:37

This question exists because it has historical significance, but it is not considered a good, on-topic question for this site, so please do not use it as evidence that you can ask similar questions here. This question and its answers are frozen and cannot be changed. More info: help center.

    
community wiki? :) –  rkthkr Jun 26 '09 at 11:12
    
It is a Wiki now –  Binoj Antony Jun 26 '09 at 11:17
1  
+1 why are people downvoting the "Hidden features of" questions? –  Wadih M. Jun 27 '09 at 4:07

11 Answers 11

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)
share
    
you can also use SHOW PROFILE ALL FOR QUERY X to show all the profiling data. –  Kedare Dec 24 '10 at 20:17

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();
share
    
+1 on md5sum. I like the md5sum - idea to use as a pager. Not really mysql specific thou –  Server Horror Jun 26 '09 at 12:18

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';
share

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

share
1  
I'm a bit puzzled by you listing well know and documented commands such as SHOW and EXPLAIN as "hidden features". Isn't a hidden feature one that is not documented? –  John Gardeniers Jul 12 '09 at 11:36
    
Well, the intention is to bring out the not so well known useful commands, what may be well known to one may be unknown to someone else, what say? –  Binoj Antony Jul 13 '09 at 4:43

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.

share

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'
share
1  
REPLACE INTO is useful, but it should be noted it performs a key check, DELETEs if a matching key is found then finally INSERTs, which takes a lot longer than INSERT...ON DUPLICATE KEY UPDATE –  Andy Jul 14 '09 at 14:44

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

share

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
share

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
share

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:

share

Just as an addition to pQD's answer (as a noobie I'm not able yet to add a comment), if you haven't yet added the path to the slow query log in the correct my.cnf file, the error log will be written to the data directory (use SHOW VARIABLES LIKE 'datadir'; to find out where this is) and the filename will be in the format [systemname]-slow.log

share

Not the answer you're looking for? Browse other questions tagged or ask your own question.