MySQL: Storage Engines, Advanced Indexing, and Maintenance

Personnel at all levels of an enterprise seeking to attain competency in MySQL.

Prerequisite
None

Expected Duration
177 minutes

Description
A well designed and high performance database depends on its storage engine and index structure. In this course, you’ll take an in-depth look at storage engines and how they affect the performance of your MySQL databases. The course also covers index structures and types, indexing for performance, and index maintenance.

Objective

Storage Engines

  • start the course
  • examine the available storage engines for MySQL databases
  • examine the features and abilities of different storage engines to best support applications
  • compare the features of two common storage engines
  • set your default storage engine and work with multiple storage engines

Index Structure and Types

  • set the internal structure of indexes
  • recognize the clustered index structure
  • recognize the secondary index structure
  • create a clustered index on a table
  • create a secondary index on a table
  • recognize how MySQL automatically defines and creates hash indexes
  • examine the various index types available in MySQL

Indexing for Performance

  • determine what types of indexes have been implemented and their properties
  • create multi-column indexes and examine their sequences
  • examine how AND and OR conditions affect query results and performance
  • examine the effect of an AND condition on query performance
  • examine the effect of an OR condition on query performance
  • recognize the use and effectiveness of a covered index
  • create a covered index to improve query performance
  • examine how to explicitly direct your queries to use a specific index

Index Maintenance

  • examine how indexes need to be maintained over time
  • examine the process which can adversely affect index performance
  • recognize how MySQL uses statistics to make the best use of your indexes
  • examine the MySQL query optimizer and how it determines the most effective method to retrieve data
  • improve index performance using drop and recreate methods
  • recognize general methods for maintaining tables and indexes
  • recognize recommendations for keeping your data and indexes current and effective

Practice: Storage Engines and Indexes

  • describe and use storage engines and describe and manage indexes

MONTHLY SUBSCRIPTION

$129/month
 

ANNUAL SUBSCRIPTION

$1295/year

Multi-license discounts available for Annual and Monthly subscriptions.