Home / server / Questions / 547438
Accepted
Gerry
Asked: 2013-10-21 19:51:18 +0800 CST

mysqldump has a "quick" option. Why isn't this enabled by default?

  • 772

In the mysqldump docs I see a "--quick" option, however there is no indication as to why somebody would want this disabled. What are the disadvantages to enabling it or why is it even an option? Shouldn't it just be always on?

http://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_quick

edit: While it's been made clear to me that it is enabled by default, this isn't too clear the from the official documentation (especially when some samples specify the option: http://dev.mysql.com/doc/refman/5.7/en/copying-databases.html). The table of options does not have a field specifying the defaults and the description of --quick has no note that it's included by --opt. I'll leave the question title as it was so that others looking for the same thing via Google can find the information easily.

mysql

2 Answers

  1. While --quick is on by default, I can only think of one specific time where I had to disable a part of the --quick options.

    A client of mine had a database that was to be installed on a smaller Linux server with almost no memory (I think it was 2GB). A mysqldump made with --quick had extended INSERTs. That mysqldump could not be loaded into this 2GB Linux because a bulk insert buffer could not be allocated large enough to accommodate a single extended INSERT. It was driving me nuts. That is, until I decided to add --skip-extended-insert.

    What this did what make each INSERT command insert one row. While this blew up the size of the mysqldump, and made the load of the mysqldump take hours, that did the trick. The entire dataset was loaded into a rinky-dink Linux server.

    CAVEAT : Please don't ask me why a client would want MySQL running on a 2GB Linux server. This was over 5 years ago, and I still scratch my head when I think about.

    As for --disable-keys, you could use --skip-disable-keys to let a MyISAM table get reloaded and let the INSERTs fill the all non-unique indexes on-the-fly. This would produce somewhat lopsided indexes if the data were loaded already sorted. Nevertheless, this would be necessary when reloading a mysqldump in a low-memory environment (as I already sadly reminisced).

    • 7
  2. Best Answer

    --quick is on by default, as it is one of the options included in --opt, which is itself on by default.

    From the man page:

    • Use of --opt is the same as specifying --add-drop-table, --add-locks, --create-options, --disable-keys, --extended-insert, --lock-tables, --quick, and --set-charset. All of the options that --opt stands for also are on by default because --opt is on by default.

    The page you linked to contains similar information.

    • --opt

      This option, enabled by default, is shorthand for the combination of --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. It gives a fast dump operation and produces a dump file that can be reloaded into a MySQL server quickly.

    • 5