in Database

Introduction

MySQL is the most popular Open Source (RDBMS) Relational DataBase Management System. It is open source which means nothing to pay for using it . MySQL is fast , works well even with large data sets. It can support large databases , up to 50 million rows or more in a table .

Before we begin , let’s talk about database storage engine . Database Storage engine is a software component that a database management system uses to create CRUD (Create, Read, Update, Delete) operation . There are other storage engine available . Some of them are listed below :

  1. INNODB
  2. MYISAM
  3. CSV
  4. BLACKHOLE
  5. HEAP or MEMORY
  6. ARCHIVE

Each engine are more suited in certain conditions more than other. There is no one best engine , you can choose the storage engine according to your application needs.

Today we’ll focus on two common mostly used storage engine’s , MyISAM and InnoDB . We’ll talk about their differences and change our database storage in Laravel according to our needs.

Getting Started

InnoDB is a default storage engine for MySQL 5.5 and above where as MyISAM is default storage engine for prior MySQL 5.5 . Enter the command below in terminal to view their support .

This will list out the engine and their supports available.

InnoDB

  1. Transaction safe storage engine (ACID transactions) .
  2. Supports foreign key referential integrity.
  3. Provides row-level locking.
  4. Crash recovery
  5. Full text indexing is not available until MySQL 5.6 .
  6. Provides high performance when working with large data .
  7. Table size can be 64 TB (Terabyte)

MyISAM

  1. Does not support Transaction .
  2. Supports Full text indexing .
  3. Provides table-level locking.
  4. Has the most flexible AUTO_INCREMENTED column handling off all the storage’s .
  5. Table size can be 256 Terabyte.

Please go through the list and choose the right database engine for you . Changing the database storage engine :

I’ve created a helper function for changing the every database table to required storage engine for Laravel . You can create Seeder or class as you want to using this helper function snippet .

Happy Coding 🙂