Eloquent Performance Patterns

Measuring Your Database Performance

Hello! To get things started, let’s discuss how to measure your database’s performance. Without doing this, you’re essentially flying blind.

Minimize Memory Usage

In this lesson, we’ll review how to minimize memory usage by only selecting the data that we actually require from the database.

Getting One Record From a Has-Many Relationship

In this lesson, we’ll review how to fetch one record from a has-many relationship in the most efficient way. It’s not enough to simply eager the desired relationship. In some cases, that can actually be worse for performance. Instead, we’ll leverage database subqueries.

Dynamic Relationships Using Subqueries

In the last episode, we learned how to use subqueries to fetch a single relationship from a has-many relationship in the most optimal way. Now, we’ll take that technique one step further!

Calculate Totals Using Conditional Aggregates

In this lesson, you’ll learn how to calculate totals in the database using conditional aggregates. To illustrate this, we’ll review a demo application that tracks customer feature requests.

Optimize Circular Relationships

In this episode, you’ll learn how to optimize circular relationships by leveraging Eloquent’s setRelation() method. This will allow us to effectively manually eager load our desired relationships.

Multi-Column Searching

Let’s now begin a new chapter, where we build out a search feature using only string matching and the LIKE operator.

Getting LIKE to use an Index

In this episode, we’ll begin optimizing the search query that we wrote in the previous episode. We’ll start by adding three indexes, which, surprisingly, doesn’t solve the problem. How come?

Faster Options Than whereHas

We still have an issue where our indexes are not properly being leveraged. In this lesson, we’ll explore some alternative options that should fix this and lead to faster query speeds.

When it Makes Sense to Run Additional Queries

In the last episode, we discussed how using a whereIn subquery can be much faster than whereHas. But now, let’s review a more radical approach for solving this problem: running multiple database queries.

Use Unions to Run Queries Independently

Let’s now review how we can achieve the same benefits of running multiple queries, but within a single database query.

Fuzzier Searching With Regular Expressions

To finish up this chapter, let’s figure out how to make our search a bit more fuzzy by combining regular expressions and virtual indices.

Run Authorization Policies in the Database

In this episode, we’ll learn when and why it might prove advantageous to run your authorization policies directly in the database.

Faster Ordering With Compound Indexes

In this lesson, we’ll look at how using compound indexes can drastically improve orderBy query performance. Let’s review an example!

Options for Ordering by a HasOne Relationship

Next up, we’ll review how we can order records by the value of a hasOne relationship. I’ll show you two possible options, and then we’ll determine which is most performant.

Options for Ordering by a BelongsTo Relationship

In this lesson, we’ll look at how we might order records from the database according to the value of a belongsTo relationship column.

Options for Ordering by a HasMany Relationship

Now, let’s review how we might order records by the value of a hasMany relationship column. As with the previous two episodes, we’ll compare joins and subqueries.

Options for Ordering by a BelongsToMany Relationship

In this lesson, we’ll use the example of a library checkout system to review how we might order records by the value of a belongsToMany relationship column.

Ordering With NULLs Always Last

In this lesson, we’ll review a few examples and solutions for when you wish to order results, where null values are placed at the end of the results.

Ordering By Custom Algorithms

Next up, we’ll review how to order records by a custom algorithm. To illustrate this, we’ll use the example of a list of feature requests that can be sorted by title, status, or activity.

Filtering and Sorting Anniversary Dates

In this lesson, we’ll learn how to sort, filter, and order information according to anniversary dates. For instance, how might you sort a list of users by their birthdate?

Make N 1 Issues Impossible

In this lesson, we’ll review one simple technique that will allow you to ensure that N 1 issues never even have the opportunity to enter your system.

Ordering Data For Humans Using Natural Sort

Next up, we’ll review a slightly more tricky database task: ordering data for humans using natural sorting. Let’s review a couple of examples.

Full Text Searching With Rankings

In this lesson, you’ll learn how to perform full text search with rankings. To allow for this, we’ll need to manually create a MySQL full text index.

Get the Distance Between Geographic Points

Next up, I’ll show you how to use geospatial functions to get the distance between two geographic points. Let’s have a look.

Filter by Geographic Distance

In this lesson, we’ll continue with the example from the previous episode about geographic points and learn how to only display results that are within a ten kilometer distance.

Order by Geographic Distance

In this lesson, we’ll make one more update to the previous episode. We’ll learn how to order locations by geographic distance so that we can see the closest stores first. Let’s have a look!

Filter by Geospatial Area

In this lesson, you’ll learn how to filter results by geospatial area. To illustrate this, we’ll set up a demo app that includes a map of Canada. And then we’ll represent each customer’s location as a point on the map.