7

Quick question: I have created come commands that will warm-up my DB. The thing is that those selects are large. When I do such a select I get all the "jiba-jaba" into my terminal window. How can you make mysql do the select 'quietly' ? without taking ages to print all the crap into my terminal ?

Katafalkas
  • 523
  • 2
  • 8
  • 20

4 Answers4

13

  1. mysql-client:

    mysql> pager > /dev/null
PAGER set to '> /dev/null'
mysql> select 1;
1 row in set (0.00 sec)

mysql> \q
Bye

    This way the result of the queries is not printed, only row count an timing information.

  2. IO redirection in the shell

    user@host$ mysql -e 'select 1' > /dev/null
user@host$
Hubert Kario
  • 6,361
  • 6
  • 36
  • 65
m.sr
  • 1,060
  • 1
  • 8
  • 19
8

You can redirect the output of any command -- not just MySQL -- by using your shell's i/o redirection operators. For example, in bash:

$ mysql mydb < commands.sql > /dev/null 2>&1

This will launch the MySQL command line client, connect it to "mydb" on the local system (assuming you have access), read SQL commands from the file commands.sql and dump all the output to /dev/null.

If you wanted to save the output for review after the fact, you could redirect it to a file rather than to /dev/null:

$ mysql mydb < commands.sql > output.txt 2>&1

The 2>&1 redirects stderr as well as stdout. If you wanted to see any errors on your terminal, you would only redirect stdout:

$ mysql mydb < commands.sql > /dev/null
larsks
  • 43,623
  • 14
  • 121
  • 180
1

If the purpose is to warm up the cache, you could also do something like:

select max(concat( col1,col2 ) ) from mytable where ...

The engine is forced to read the real pages to compute the max (with little overhead relative to the I/O anyway)

Note that if you select only columns from an index, only the index is red, there is no need to go to the table itself ("cover index").
In some cases you may want exactly that (load only the index into the cache)

phil_w
  • 111
  • 2
0

That is working on Unix installations of mysql. Since Windows does neither has /dev/null nor a pager command in the mysql installation it will not work on mysql installed on windows.

On Windows Mysql (on Unix as well) you can use SELECT 1 INTO OUTFILE 'file_name' or set a variable and use SELECT 1 INTO @var; Check out the documentation here: http://dev.mysql.com/doc/refman/5.0/en/select.html

Keep in mind, that you cannot override an outfile so INTO Outfile will not work for repetitive selects.

mtherocket
  • 1
  • 1