Some developers don't even know what MySQL storage engine is, and they don't need to know that today because the decision on what to use is made for them by framework developers. For example, Drupal uses InnoDB for its tables. It was not always that way though. I remember the time when we had to convert Drupal 5 tables into InnoDB on a site with intense load. It was so long ago, maybe in my previous life.
Until today, I thought (or I wanted to think) that all modern PHP frameworks use at least InnoDB by default (there are newer storage engines). It turned out that I was wrong. Today I noticed that tables of the Laravel 5.2 application I worked on were in MyISAM. You can't even imagine my confusion.
The main reason I decided to move to InnoDB is that the application of my client has a few threads (scheduled commands), which are reading from and writing into the database simultaneously. I can't simply afford table-level locks of MyISAM. So, first of all, I set InnoDB as a default storage engine of the application through config/database.php so that new tables would be created in InnoDB.
'connections' => [
'mysql' => [
'engine' => 'InnoDB',
Alternatively, I could set storage engine through migrations for new tables the following way:
Schema::create('users', function($table)
{
$table->engine = 'InnoDB';
// ...
});
All my existing tables were in MyISAM, so I needed to alter the tables and change their engines. The sad thing was that I did not find a way to do that through Laravel's Schema Builder. Luckily, I knew a solution for all limitations of the Laravel database layer. It's DB facade, which allows to execute low level MySQL queries. Something like this:
DB::statement('ALTER TABLE users ENGINE = InnoDB');
So, the final code of my migration looked similar to this:
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class ConvertTablesIntoInnoDB extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
$tables = [
'users',
'products',
];
foreach ($tables as $table) {
DB::statement('ALTER TABLE ' . $table . ' ENGINE = InnoDB');
}
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
$tables = [
'users',
'products',
];
foreach ($tables as $table) {
DB::statement('ALTER TABLE ' . $table . ' ENGINE = MyISAM');
}
}
}
I'm not a Laravel ninja but the solutions I have just mentioned worked for me, and I hope they can work for you.