Purpose of MySQL Command-Line Options -ANe

MySQLscripting

I've recently changed how a mysqldump backup is set up, and happened across a helpful answer by RolandoMySQLDBA where his example:

mysql ${MYSQL_CONN} -ANe"${SQL}"

Uses the -ANe command line switch to precede the query to execute.

I understand that these have the following effects:

  • --no-auto-rehash, -A

    Disables auto-rehash, which is described as: Enable automatic
    rehashing. This option is on by default, which enables database,
    table, and column name completion. Use --disable-auto-rehash to
    disable rehashing. That causes mysql to start faster, but you must
    issue the rehash command or its # shortcut if you want to use name
    completion.

  • --skip-column-names, -N

    Do not write column names in results.

  • -execute=statement, -e statement

    Execute the statement and quit.

Obviously, I understand that since we are executing a query, the -e argument is required.

The query I am executing is changing the global variable to enable/disable the slow query log. I'm not quite sure how omitting column names in results is important in this case. I'm less clear on how disabling auto rehashing is necessary or precisely what it is doing. I understand from the description that it causes mysql to start faster, but since the mysql process is already running, isn't that ignored?

In short, what are the -A and -N arguments for in the context of changing a global variable via shell script?

MySQL 5.6.4 on Ubuntu 14.04 LTS

Best Answer

The biggest reason was already stated by VĂ©race, but I wanted to add to it

When a database instance has lots of tables and lots of columns, the information_schema has to inspected in memory. This can be a cause for alarm. Why ?

Back in April 2014, I wrote the answer to Do Inactive MySQL Databases Consume Memory? and Adding new tables -- memory usage increases.

I have seen MySQL instances with 800+ databases with each database containing 162 tables.

Imagine loading metadata for the DB you connect to. Lots of needless prep work for the beginning of a DB Connection's LifeCycle along with allocating memory for a local copy of that metadata in addition to per-session buffers (See my post How costly is opening and closing of a DB connection?)

This is the biggest reason why I use -A to connect to do little things.