Laracon EU 2018 Logo Speakers Conference
Buy tickets
Kai Sassnowski - Speaker at Laracon EU 2018

Kai Sassnowski

Software Developer at wycomco

Talk

Things every developer absolutely, positively needs to know about database indexing

This talk dives deep into a topic that I believe developers don't know enough about: database indexing. There are four sections to this talk: - What's an index? * How to create an index? * Understanding the execution plan * Common pitfalls What's an index In the first part we will learn what an index actually is. And we're talking data structures. How is an index actually represented internally and what are the implications of that. This is important because it makes certain characteristics of indexes much easier to understand. We will refer back to this when we talk about common pitfalls later on in the talk. How to create an index? A really quick review about how to create an index. Just a refresher, really. Understanding the execution plan. This is the meat of the talk. We will learn how to read and understand the MySQL execution plan (the output of the EXPLAIN statement). This is the way to debug slow queries. We will take a look at the different "Access Types" that might appear in the execution plan and what they mean (const/eq_ref, ref/range, index and ALL). This part of the presentation happens with a live coding example. We will take a look at a database with about a million rows and continuously improve our queries. As we do that, we will learn about common pitfalls when using indexes. These include: - functions on columns in your WHERE clause - indexing wildcards - the order of columns in a multi-column index - inequality operations The goal of this talk is not so much to turn the listener into an expert about indexing, the topic is simply too large for that. However, I hope to show people that there is a lot more to be learned about database indexing than they might have thought. Since slow queries are one of the most common causes of poor application performance I think it is absolutely crucial for developers to be properly educated about this topic.

Subscribe to the Laracon EU mailinglist