MySQL in 3 Hours & MyISAM vs. InnoDB

A major factor in database performance is the storage engine used by the database, and more specifically, its tables. Different storage engines provide better performance in one situation over another.

For general use, there are two contenders to be considered. These are MyISAM, which is the default MySQL storage engine, or InnoDB, which is an alternative engine built-in to MySQL intended for high-performance databases. Before we can understand the difference between the two storage engines, we need to understand the term “locking.”

An application or website that has a frequently used table works exceptionally well using the InnoDB storage engine by resolving table-locking bottlenecks. However, the question of using one over the other is a subjective as neither of them is perfect in all situations.

There are strengths and limitations to both storage engines. Intimate knowledge of the database structure and query practices is critical for selecting the best storage engine for your tables.

MyISAM will out-perform InnoDB on large tables that require vastly more read activity versus write activity. (now used on aatventure.news, also because indexing is possible on varchar(500))

MyISAM’s readabilities outshine InnoDB because locking the entire table is quicker than figuring out which rows are locked in the table. The more information in the table, the more time it takes InnoDB to figure out which ones are not accessible.

If your application relies on huge tables that do not change data frequently, then MyISAM will out-perform InnoDB. Conversely, InnoDB outperforms MyISAM when data within the table changes frequently.

Table changes write data more than reading data per second. In these situations, InnoDB can keep up with large amounts of requests easier than locking the entire table for each one.

0:00:00 - Introduction

0:01:02 - What is SQL?

0:04:24 - Cheat Sheet

0:04:50 - Installing MySQL on Mac

0:09:48 - Installing MySQL on Windows

0:15:08 - Creating the Databases for this Course 

0:23:40 - The SELECT Statement

0:29:30 - The SELECT Clause

0:38:18 - The WHERE Clause

0:43:35 - The AND, OR, and NOT Operators 

0:51:38 - The IN Operator

0:54:41 - The BETWEEN Operator

0:56:53 - The LIKE Operator

1:02:31 - The REGEXP Operator

1:11:51 - The IS NULL Operator

1:14:18 - The ORDER BY Operator

1:21:23 - The LIMIT Operator

1:24:50 - Inner Joins

1:33:16 - Joining Across Databases

1:36:03 - Self Joins

1:40:17 - Joining Multiple Tables

1:47:03 - Compound Join Conditions

1:50:44 - Implicit Join Syntax

1:53:04 - Outer Joins

1:59:31 - Outer Join Between Multiple Tables 

2:05:50 - Self Outer Joins

2:08:02 - The USING Clause

2:13:25 - Natural Joins

2:14:46 - Cross Joins

2:18:01 - Unions

2:26:29 - Column Attributes

2:29:54 - Inserting a Single Row 

2:35:40 - Inserting Multiple Rows 

2:38:58 - Inserting Hierarchical Rows 

2:44:51 - Creating a Copy of a Table 

2:53:38 - Updating a Single Row 

2:57:33 - Updating Multiple Rows 

3:00:47 - Using Subqueries in Updates 

3:06:24 - Deleting Rows