Force django to create tables using MYISAM storage engine

Question

How to force django to use MYISAM storage engine when creating database using syncdb command? This page does not help much to shed light in this issue.

MYISAM is the perfect choice since the database is almost only used for reading and MYISAM is significantly faster that InnoDB. There are still some models.ForeignKey fields in the model, but they are only being used to create master detail admin pages. There is not need of having the actual foreign keys in the database.

Famished

Answer 1

See this page. Using an OPTIONS variable in your DATABASES setting should do the trick, but migrating an existing database to a new engine isn't so easy (think you'd have to re-initialize).

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

Snaggletooth

A hint on migrating an existing db: you'll probably want to dump to something like yaml, and import that back in after you recreate the schema. – Vulpecula

Should use default_storage_engine instead of storage_engine for mysql 5.7+ – Atmospheric

Answer 2

You should set the storage engine as INNODB in the database definition directly, like this.

DATABASES = {
   'default': {
       'ENGINE': 'django.db.backends.mysql',
       .....
       'STORAGE_ENGINE': 'MYISAM'
   }
}

For New Version you can use:

DATABASES = {
       'default': {
           'ENGINE': 'django.db.backends.mysql',
           .....
           'OPTIONS': {
                     "init_command": "SET storage_engine=MYISAM",
                      } 
       }
    }

Absorbing

for mysql 6 you need DEFAULT_STORAGE_ENGINE – Examen