Why remove django DATABASE_OPTIONS's "init_command set engine=INNODB" after table creation?

Question

Docs on creating your database tables says:

Another option is to use the init_command option for MySQLdb prior to creating your tables:

DATABASE_OPTIONS = {
   "init_command": "SET storage_engine=INNODB",
}

This sets the default storage engine upon connecting to the database. After your tables have been created, you should remove this option as it adds a query that is only needed during table creation to each database connection.

Does anyone know why is it necessary to remove this option after table creation?

Pianism

Answer 1

Typically permissions and settings are tree based. Your settings for the session will be a pointer to the default settings one level above yours. You session settings are going to be already created and just referencing the default settings when you first connect.

When you modify a setting, such as by setting the storage_engine value, you are either creating a new copy of all of the settings and changing one value (as in Apache) or adding another layer to the tree that it has to check in when resolving values. I am not actually sure which one MySQL uses, but either way, if you do not need this setting, you should not set it on every round trip.

If you do need it relatively frequently, it might be worth the performance hit. A similar issue occurs in PHP. You do not want to modify variables like the PHP include path in your PHP code, that used to add a ton of overhead.

Jacob

Sepal

Answer 2

Syntax has changed as of django 1.2

DATABASES = { 
  'default': {
    'ENGINE': 'django.db.backends.mysql',
    'NAME': '',                      
    'USER': '',     
    'PASSWORD': '',
    'OPTIONS': {
           "init_command": "SET storage_engine=INNODB",
    }   
  }   
}

Devilfish

Answer 3

Removing this option to make things efficient - you don't need to set storage engine every time you connect to database, only when you are creating tables (i.e. syncdb, south).

Hoodmanblind

Answer 4

If you have other options, eg:

DATABASE_OPTIONS = { "init_command": "SET storage_engine=INNODB, wait_timeout = 30, time_zone =... ", }

then it doesn't hurt to leave the default storage engine set.

Lenardlenci