O'Reilly logo

Understanding MySQL Internals by Sasha Pachev

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Interesting Aspects of Specific Configuration Variables

Now that you understand the general handling of configuration variables, this section presents the stories of particular variables that affect mysqld significantly.

big-tables

The MySQL optimizer tries as hard as it can to avoid using a temporary table when resolving a query. However, in some cases this grim task just has to be done. Then, if at all possible, it will try to use an in-memory temporary table. Unfortunately, the size of the table cannot always be estimated in advance. Sometimes in the process of populating the table, the maximum in-memory table size limit is reached (the limit is controlled by the tmp_table_size setting). When this happens, the temporary table needs to be converted to a disk type (i.e., MyISAM). This means re-creating the table and repopulating it with the rows collected in the in-memory table up to this point.

For a typical MySQL usage, the need to convert an in-memory table to disk is a rare occurrence. However, there are applications that run into this situation a lot. If you know in advance that the temporary result is going to be more than can be stored in memory, the big-tables option comes in handy. It tells the server to not even bother creating an in-memory table, and to start with a disk-based table right away.

When enabled, big-tables can still be overridden with the SQL_SMALL_RESULT query option for one particular query. Alternatively, when big-tables is disabled, SQL_ BIG_RESULT option ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required