Business vector created by fullvector — www.freepik.com

Tips to speed up database query in Laravel

Have you ever been wondering why your application slow even tough you already fix your application code?

Performance of the application is not depend on application itself. Application and database need to be tune together. For example, imagine 100 customers enter very big fancy restaurant (application) and there is only 2 waiter available (database). How can 2 waiter serve all the customer without keeping the customer in queue for long period? End up, the customer left the restaurant and never visit again.

Here is my 2 cents of tips to speed up database query based on my experience.

Table Indexing

Indexes is the most important part in Database. Indexing allow data retrieval efficiently faster. Indexes are used to quickly retrieve data without having to search every row in a table every time a database table is accessed. Small database wont be noticed but once it grows, the query getting time consuming.

How to use? All you need is to identify which column you want to take advantage from. For example, you have 1.3 million rows of users and you want to grab an email.

Without index, the process will be from top to bottom of the user data until it the email is found. Here is some comparison between with index and without it.

Without index — 750ms

With index — 2ms (400x faster)

In Laravel, the easy way is create a migration file to alter your desire table

Or if you currently on fresh project just add index to column you want to

Note that,

  1. Index is not only for single column. (Refer snippet above)
  2. Speed of the query is depends on your Database CPU/Memory. If you have potato database server, don’t expect too much

Eloquent ORM Eager Loading

Eloquent Object Relation Mapper (ORM) makes managing and working with these relationships so easy rather than manually join each table. In order to use Eloquent, there is some you need to consider to use it carefully which is Eager loading. Wrong implementation cause N+1 query. Here is where common developer do which cause N+1.

For example:

Lets fetch users about 100 rows from the table and see what happen here,

Mistake 1: Common mistake used by Laravel developer

Why? Because, the query actually runs once to get all the users BUT for each loop, its query another query for devices for 100 times. So, here is 100 + 1 = N+1 problem. Imagine if you want to fetch thousand of data. If one query cost 100ms * 100 = 10,000ms it takes to complete process.

Mistake 2: Wrong Manipulation relation table using append attribute.

Why? Because, the query actually runs once to get all the users BUT the moment Laravel want to transform it into collection mapping from appends attribute, the collection queried another 100 query. Its the same as Mistake 1 because it produce N+1 problem.

Solution

Simple mistake can cause a lot of trouble. It can be solve be using with method in Eager loading in one go.

For example

With this, instead of hitting database N+1, with method will single query joining those table and transform it into collection. So you can freely access the collection object without hitting the database. For more info about eager loading you can refer Laravel docs

Query Builder

Using eloquent is great because its have many cool features but when it come to the speed, it slightly slower than query builder because of ORM.

Here is the example comparison between eloquent and query builder against 1.3 million rows

Eloquent

Query Builder

Based on the above comparison, Query builder are most likely 2–3 times faster than Eloquent. It doesn’t mean you can’t use Eloquent. I recommended to Eloquent but in certain cases, for simple approach like data retrieval without any complexity, you might need to use Query builder.

Paginate query

Paginate as many as you can for data listing (limit result per query) because its faster rather than retrieve all data at once. If you have 100 rows, it would be fine. But what if you what millions for rows. The best practice for data listing is to use pagination for best UI/UX.

In Laravel, there is 2 pagination — simplePaginate and paginate. Let’s test it against 1.3 million rows

Why paginate much slower like 70% slower?

Let’s drill down code snippet from Illuminate\Database\Eloquent\Builder

As you can see above snippet, at the line 21, the method paginate have extra counter to count all the rows in the tables while the simplePaginate not doing that. Like I said, small data won’t be effect. If you have large dataset, you might consider simplePaginate.

But, most cases, you actually need to use paginate to create table pagination with numbers right? Then, i would suggest you create a Macro method against Builder class which fix the count method.

Based on experience, most likely if you have a lot of data like 10 million+, it shouldn’t go more than 10 seconds. If YES, then you should check your database infrastructure OR most likely your code

Always query what you really need

Here is also important to query only what you need. For example, let’s test on select query

Once again, comparison between Eloquent and Query Builder. Clearly Query builder much faster but the point is where to select what column you actually need is much-much faster. There is several reasons why. You can refer here for more info.

Same goes to the eager loading, don’t join table if you don’t need it. Some of developers most likely use $with variable in Model. My advise, just don’t. You need to manually choose which table you want to eager load instead of automatically bind it.

If you have a logic condition to check data existence in other table, don’t join it, use whereHas instead

Query Caching

Caching results of the queries which frequently accessed would increase the performance of an application. Cache basically store queries made for the first time and use it for the next request for some period of time or forever stored.

Let test with this simple query

Without cache: 985ms (with index 17ms)

With cache: 0.4ms

Cache is faster than anything. Please avoid to use file or database as cache. File will produce high I/O and database will cause high connection — slow if high traffic. Recommended to use Redis or Memcached because it store in memory.

Here is some package you might to use to ease your implementation

Database Tuning

The purpose of tuning is where we need to find the best resources for the database. Best database need to be tune. It always not come by default. You need to check this 4 resources— CPU, RAM, Storage and Network. Lack of this will result poor performance on your application. It doesn’t mean if you have high spec of CPU and RAM, you can support high traffic with slow network.

My advise is always try and error. Do the load testing on your database so that you will know the capabilities of your database. Fine tune every possible configuration can be configure and see what happen.

Always use latest version

It’s important to always use latest version of framework or database. Most update will give you performance upgrade also.

Why important?

  1. Give you security updates
  2. Keep your application running smoothly
  3. Legacy system sometime give you problem in the future
  4. Increase performance

For example in performance,

The benchmark between MySQL 5 and 8 which MySQL 8 give huge difference in the time it takes to process transactions and improvement in high workload.

For Laravel, there is also different in performance between L7 and L8 which based on my test is 2x faster than L7 and 4x faster than L6.

So, database and programming framework need to upgrade along (unless you are not using framework). Do benchmarking on multiple version and see how its going.

Separate Database from Application Server

Traditionally and most commonly found, entire environment puts on a single machine — where application, web server, database, and etc.. all together using same resources. Commonly used stack is LEMP (Linux, Nginx, MySQL, PHP).

digitalocean.com

Pros?

  • Quick and easy for simple setup in little scalability.

Downside?

  • Shared resources — lead to poor performance
  • Difficulties to determine the source of poor performance
  • If the machine shut down, everything gone

What related to this topic? Shared resource cause database unable to allocate process properly. If the application use 70% of CPU, database only have 30% or less just to process queries which lead to long query problem and timeout.

digitalocean.com

So, separating the database from the application server would be better. Why? By splitting database from application server, we can properly allocate service only for database and also increase the security by put it in private network.

Benefits?

  • Fully optimized number of connection without been overloaded by other processes.
  • Security purpose by isolated it from public network.
  • Problem diagnosis and performance monitoring is easier.
  • Maintenance and upgrade much easier when only focus on database without worrying other component

I would recommended to use cloud provider (AWS, Alibaba, DigitalOcean) when it comes to splitting environment because cloud provider have shared responsibility where you only need to care about your application and data. For maintaining the hardware and software is cloud provider responsibility.

Use Read-only database to retrieve data

If you have enough cost, splitting the database into read-only database would be good. Its provide flexibility, error tolerance, speed, and no locking occurred. Furthermore, read-only provides enhanced security for databases by protecting them from accidental or malicious updates.

So you will have 2 connection string. 1 — Read-Write database and 2 — Read-Only database. Frequent access data retrieve you might need to use readonly database. For less data retrieval and frequent update use the read-write database.

Conclusion

This is just several tips what i can help developers out there. Thanks for reading and don’t forget to share with you friends.

That’s all. Thanks for your time ~~

References

Laravel Documentationhttps://laravel.com/docs/8.x/eloquent-relationships

Query Performancehttps://tanelpoder.com/posts/reasons-why-select-star-is-bad-for-sql-performance/

Server architecture separationhttps://www.digitalocean.com/community/tutorials/5-common-server-setups-for-your-web-application

Software Engineer at Teratotech.com

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store