We've finished indexes, we're going to move on to queries! This is an overview of the queries section.
1:08
Learning how to read and interpret MySQL's EXPLAIN statement is a vital skill when it comes to writing performant queries. Here we'll look at the basic EXPLAIN output.
11:45
In this video we dive deep into the `type` column from the EXPLAIN output. This is one of the most important pieces of information that the EXPLAIN statement gives you.
7:43
The EXPLAIN ANALYZE format gives you a lot more detail to work with as compared to the traditional EXPLAIN output. It can also be a little bit intimidating.
7:02
Wrapping your columns in a function can lead to something called index obfuscation, which prevents MySQL from using your indexes.
4:04
Despite your best efforts, it may be impossible to index a condition correctly. In that case, you might be able to use a redundant condition to help narrow down records quickly.
6:23
One of the oldest pieces of DBA wisdom is to select only what you need. This is extremely good advice, as long as your application layer understands what's happening!
8:10
Only asking the database for the data that you actually need is a great way to ensure that your queries remain as performant as possible.
9:51
Joining multiple tables together is a fundamental capability of databases. Here we'll cover the different types of joins and how to use them.
9:34
Knowing how to join tables is important, but knowing how to do it efficiently is just as important. Here we'll revisit foreign keys and how they are used in joins.
13:26
Subqueries are a powerful tool for eliminating or including records based on the results of a secondary query. Fortunately MySQL includes optimizations for this very situation!
9:48
Common Table Expressions are a powerful way to refactor complicated queries into more readable versions. MySQL also provides a few optimizations for them beyond what subqueries offer.
9:16
Recursive CTEs can be used to generate completely new data or work with your existing data. In this video we'll cover both use cases.
11:54
Joining tables puts the results together side-by-side, while unions put the results together one on top of the other. We don't use them very often, but they can be super helpful.
5:46
Window functions allow you to work on a subset of rows related to the current row, while the query is being processed. They allow you to produce ranked lists, calculate user journeys, and more.
9:27
Often times you want your rows returned to you in a certain order. Here we'll talk about deterministic ordering and why it's so important.
6:11
Sorting your queries is not free, but we can make it a lot cheaper with indexes. Rule number one is always don't sort your rows if you don't need them sorted though!
8:42
When using composite indexes you must remember the rules for access. Those same composite index rules apply for sorting as well!
5:58
In this video we'll cover how to count rows, values, and multiple things all in one query.
5:29
When dealing with nullable columns there are a few extra things you need to consider, which we'll cover here!
Feedback or questions? Reach out to our team at education@planetscale.com.