Course info
Aug 26, 2013
1h 39m

Performance is one of the most essential aspects of any application. Everyone wants their server to perform optimally and at the best efficiency. Indexes are the silver bullets for Performance. In this course we will gain an understanding of the basics of Indexes and further explore practical tips and tricks for performance tuning.

About the author
About the author

Pinal Dave is a Pluralsight Developer Evangelist.

More from the author
Getting Started with MariaDB
2h 23m
Nov 2, 2018
Monitoring MySQL with Performance Schema
1h 59m
Apr 28, 2017
More courses by Pinal Dave
Section Introduction Transcripts
Section Introduction Transcripts

MySQL Indexing for Performance
Welcome to MySQL Indexing for Performance course. This is Pinal Dave for Pluralsight. We'll be walking together on the journey to learn MySQL Indexing. Indexing is considered to be a complex subject. I promise that I will make it as simple as possible for everybody to learn indexing. Here is a scenario from a typical workday of any developer. When any application gets slower, a manager runs to a developer expressing the queries are running slow on a server. A typical expression from the developer is create an index on the slow running queries. Honestly, this is a very tricky answer. If you ask me, indexes are double-edged swords. If you use it properly, you are guaranteed to get optimal performance. However, if you implement it incorrectly, you are once again guaranteed to get a worse performance. More than a technique, I like to call indexing an art. Just like any other art, you need to practice a lot with indexes to master it. In this course, first we'll be covering various different types of indexes. Right after that, we will learn with the help of lots of demonstrations how to get high performance from any query with the help of indexes. A beautiful piece of art has to be maintained if you want to preserve the masterpiece for a long time. Just like that, we have to maintain the indexes to get optimal performance forever for any application. Before we end this course, in the last module we will go through the checklist for getting optimal performance from any query with the help of indexes.

Types of Indexes
Hi, this is Pinal Dave for Pluralsight, and in this module we will discuss various different types of indexes. There are different types of indexes in relational theory. Each index is designed to achieve a different goal. Old indexes, which we are implemented at the database storage level instead of database server level, hence, each index is different from each other and there is no single way to implement indexes in MySQL. In simple words, the inner workings for each index are quite different from each other. In this module, we will take a look at all the different types of indexes which MySQL supports and their advantages, as well as disadvantages. Before we start the module, we'll discuss about InnoDB and MyISAM storage engines. Later, we'll understand how B-tree indexes are implemented and their advantages. Right after that, we'll discuss clustered indexes, their advantages, disadvantages, and building blocks of clustered indexes. We will also understand how secondary index B-tree works. In some engines, it is also known as non-clustered indexes. Hash index with MySQL is also a very interesting concept. We will also understand various limitations of hash indexes, how adaptive hash indexes work, and what are the inner workings of hash indexes. We will end this module with the discussion of other index types.

Indexing Strategies for High Performance
Hi, this is Pinal Dave for Pluralsight, and in this module we'll discuss Indexing Strategies for High Performance. Here is our outline. We will cover the effectiveness of indexes, basis of indexes, order of columns in indexes, optimizing for OR and AND condition, how to implement cover indexes, and finally we will cover clustered indexes and non-clustered indexes. This is a demo-oriented module. Before we jump to demonstrations, let's quickly see the effectiveness of indexes.

Index Maintenance
Hi, this is Pinal Dave for Pluralsight, and in this module we will discuss about Index Maintenance. So far we have learned how to create the most optimal index for any query. It is as essential to maintain that index at optimal performance as much as it is to create the best index for a query. In this module, we will see various tips and tricks, how we make sure that indexes always perform at its best all the time. First, we will quickly understand MySQL Query Optimizer, and right after that we will understand how to maintain index statistics. Fragmentation is one of the main causes for slower performance. We will understand how we can remove data and index fragmentation in this module. We will also cover other maintenance tips and tricks. Let's start this module by understanding MySQL Query Optimizer.