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
- 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...