MySQL: Indexes

Name: MySQL: Indexes
Author: Kati Gordon

How to understand the purpose of MySQL indexes.

Preview the first lesson free — get full access to all 5 lessons.

Course: On-Demand

Welcome back to advanced SQL. In this lesson, we will discuss indexes. We will begin by looking at the purpose of an index. Next, we'll look at two main types of architectures. And finally, we'll see how indexes come into play in our search query. Now let's think about our cookbook example. If we had to search it page by page we'd call that sequential searching. You can see how inefficient that would be. This is where indexes come in, but indexes are more than just the automated ones applied to your primary key. There are a variety of indexes used with different types of architecture and indexing methods. There are two main types of architecture to be aware of. Clustered and non-clustered. Consider the index at the back of your cookbook, it's clustered ingredients in alphabetical order. The cookbook itself is likely clustered around meal types such as salads, main course, desserts. In the case of a database, a clustered index is an index which physically orders the data on the disk in a certain way. Each time new data is added, it is saved in the same order. That's something to remember with the clustered index is that only one can be applied to a database table. You see, they enforce a data order. With this means the right time is increased because each time new data is added it all has to be rearranged. The clustered index does however greatly increase the reading speed of the table. If we said there are two types and one is clustered, then the other must be non-clustered. These types of indexes are the ones we most commonly use but not everyone knows how they're implemented. Non-clustered indexes keep a separate ordering list that has pointers to the physical rows. It's basically like the table of contents for a book. It knows on what page a certain chapter starts and ends. A table can have many non-clustered indexes but each one adds time to how long it takes to write the data. Now that we understand the index structure let's look at how it works in PostgreSQL. We can manipulate indexes in PostgreSQL via a set of commands. PostgreSQL creates a query plan for each query it receives. You should always select the plan that best adheres to the query structure and the properties of the data. We can produce a query plan using a simple command, explain. This will show us all the relevant bits of info. Let's take a look at query plan that returns all the titles in our collection. In the parentheses, we notice a couple of values. The cost of a query is a range of arbitrary units. Starting from the expected before the output phase can begin to the total estimated cost of this query.

Advanced Provider : Kati Gordon 5 Lessons · 16m in Arabic, German, English, Spanish, French, Portuguese, Chinese

Course Description

In this module, you’ll examine the purpose of indexes in PostgreSQL. Indexes can be compared to a cookbook because they help you find information in a table, and this module will help you understand their purpose. There are many types of indexes in PostgreSQL. In particular, you’ll take a close look at B-tree, bit map, and hash indexes. In this module, you’ll also examine how to add different types of indexes to your query plans. This module also touches briefly on generalized inverted indexes (GIN) and the generalized search tree (GiST).

What You'll Learn

Examine the purpose of indexes and how they help locate information in a table
Compare B-tree, bitmap, and hash indexes
Add different types of indexes to your query plans
Understand the purpose of generalized inverted indexes (GIN)
Understand the purpose of the generalized search tree (GiST)

Key Takeaways

Indexes serve to help you find information in a table, much like a cookbook helps you locate information.
There are many types of indexes, including B-tree, bitmap, and hash indexes.
Different types of indexes can be added to query plans.
The module also covers generalized inverted indexes (GIN) and the generalized search tree (GiST).

Frequently Asked Questions

What does this module cover?

This module examines the purpose of indexes, taking a close look at B-tree, bitmap, and hash indexes, how to add different types of indexes to query plans, and briefly touching on generalized inverted indexes (GIN) and the generalized search tree (GiST).

What lessons are included?

The lessons are Indexes, B Tree Indexes, Bitmap Indexes, Hash Indexes, and Index Wrap-Up.

What skills does this module focus on?

It focuses on database engines, database indexes, IndexedDB, indexing, PL/pgSQL, and PostgreSQL.

How are indexes described in this module?

Indexes are compared to a cookbook because they help you find information in a table.