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 :
- HEAP or MEMORY
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.
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 .
mysql -u username -p
This will list out the engine and their supports available.
- Transaction safe storage engine (ACID transactions) .
- Supports foreign key referential integrity.
- Provides row-level locking.
- Crash recovery
- Full text indexing is not available until MySQL 5.6 .
- Provides high performance when working with large data .
- Table size can be 64 TB (Terabyte)
- Does not support Transaction .
- Supports Full text indexing .
- Provides table-level locking.
- Has the most flexible AUTO_INCREMENTED column handling off all the storage’s .
- 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 🙂