15
votes

In the long tradition of having hidden features, let us have a list of hidden features in MySQL.

Do put one feature per answer.

Also See:
Hidden Features of Linux
Hidden Features of PowerShell
Hidden features of Oracle Database
Hidden Features of Windows 2008
Hidden Features of Solaris/OpenSolaris
Hidden Features of SQL Server
Hidden Features of IIS (6.0 / 7.0)

0

11 Answers 11

9
votes

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)
1
  • you can also use SHOW PROFILE ALL FOR QUERY X to show all the profiling data.
    – Kedare
    Dec 24, 2010 at 20:17
8
votes

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();
1
  • +1 on md5sum. I like the md5sum - idea to use as a pager. Not really mysql specific thou Jun 26, 2009 at 12:18
4
votes

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';
3
votes

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

2
  • 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? Jul 12, 2009 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? Jul 13, 2009 at 4:43
3
votes

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.

3
votes

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'
1
  • 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, 2009 at 14:44
2
votes

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

1
vote

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
1
vote

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
0
votes

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:

0
votes

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .