When it comes to the performance of the Laravel application, by far the no.1 problem I've seen is the database. DB Structure, Eloquent/SQL queries, and configuration - they all may cause many issues, so in this article, I've tried to compile "the ultimate guide" of what you need to know.
What we will talk about:
- Database Structure
- Indexing Columns
- Most Typical Eloquent Mistakes
- Eloquent vs Query Builder vs RAW Queries
- Caching
- MySQL Config
- Other DB Engines or NoSQL
So, prepare for a long read, with a lot of related links. Let's go!
First Things First: Database Structure
Even before looking at Laravel, or even PHP code at all, we need to take a look at how our database is structured. Laravel is just a layer that would execute SQL queries on the structure that we had created and which would be hard to change.
So, when planning your database, you need to ensure that the structure itself is optimized. The problem is that there's no single way to build "the best" structure. Of course, it would be good to follow the DB normalization forms, but the real scenario depends on what operations you would have.
Here are just a few potential questions to consider:
- What tables/columns would be the most queried? (candidate for separating data into "main" and "secondary" tables)
- Will you filter by X column or just store data there? (candidate for JSON columns)
- How likely it is you would need to add X similar tables/columns in the future? (candidate for polymorphic relations)
- Will you have more insert or select operations? (candidate for NoSQL DB)
And you know what most developers don't do?
They don't talk enough to the BUSINESS guys who are responsible for the business logic of the app. Project owners should predict what may happen in the future, and their words should lead the technical DB structure decisions. Not the other way around.
I have a separate full 2-hour course called How to Structure Databases in Laravel, but let me give you one example.
THE QUESTION: BelongsTo or BelongsToMany?
This is one of those "million-dollar" questions that is not asked often enough...
Premium Members Only
This advanced tutorial is available exclusively to Laravel Daily Premium members.
Already a member? Login here
Premium membership includes:
Comments & Discussion
Many thanks for the great course! I wonder why (not) or when (not) to use SQL Views instead of Eloquent Relationships.. Maybe I've missed it somewhere, did you cover this previously?
Hi. Very helpful article.
A question:
This:
$books = Book::select('title', 'author')->get();
and that:
$books = Book::all(['title', 'author']);
are the same?
Which do you think is the best option? More generally I see that everyone suggests avoiding all().
thnx!
Nice article ! Thanks. In the n+1 query optimization in your code :
// Wrong way:
$books = Book::all();
// Right way:
$books = Book::with('author')->get();
I'm wondering how laravel attach each book to the corresponding author in the collection. I supposes the two queries run are :
SELECT * FROM books /*here we get all the autor_id (ex: 1,2,3,4,5)*/
/*then all the authors are selected at one :*/
SELECT * FROM authors WHERE id IN (1,2,3,4,5)
How is the attach made inside eloquent ?
It's a bit harder to answer this question as it requires a very deep dive into the Eloquent itself.
If you are curious - here's the spot that load it: Eloquent Builder
From what I understood, it's pretty much loading the related table with ID's list, then simply working with the collection to group by parent ID and assign to parent models. Or something very close to that idea (again, not 100% sure) :)
Great article, as always!
Here I have noticed:
return Book:where(...)->where(...)->get();
})```
I think it should be:
```$books = cache()->remember('homepage-books', $secondsToCache, function() {
return Book::where(...)->where(...)->get();
})```
nice article. i learned a lot Povilas. thank you for this!