Database speed killers I often see

Searching for performance enhancements

I’m building web applications for somewhat 20 years now , and i’ve been dealing with many speed issues through time. Issues that made me a better developer, but issues I often see coming back in code examples and pull requests from other developers. Here are some tips how to avoid this speed issues.

Most of the time we have to deal with related data. I’ll give an example … A user has posts and post have comments. These relations will be defined in your eloquent models with hasMany relationships. Ok so far so good.

Now when selecting this data, I often see the following construction , what’s looks good at first sight.

$users = Users::all(); foreach($users as $user){ foreach($user->posts as $post){ echo $post->content."
"; foreach($post->comments as $comment){ echo $comment->content."
"; } } }

Ok well done , we are showing all the posts and comments for all users. Job done … No not really. Here we deal with the N+1 problem. For every user ( 1 sql ) , all posts ( a sql per user ) , and comments ( a sql per post ) will be fetched from te database. Assume we have 10 users with 10 posts and 10 comments we will have 1 + ( 10 * 10 ) sql statements. So we execute 101 queries against our database. If we have 100 posts with 10 comments … 1001 statements ?

This can be done better with eager loading, by only changing one line of code.

$users = Users::with('posts.comments')->get();

This will result in the same datasets, but it will be done in 3 queries … 1 per model.

More info can be found in the Laravel docs here

We like stars ( * ) , don’t we

Assume you have a huge database table , with a lot of columns and you perform a select statement on it retreiving models. You will typically perform some search logic and perform a ->get() on to retreive your models. This will return all columns from your database loading a massive dataset in memory. It’s much better to specify the columns you want by add an array of fields you want to return. For example ->get(‘title’,’comment’) , this will only return the data you want, using less memory.

A database index ? Que !?

unfortunately , many developers don’t think about indexes. Indexes make your database much more performant , it tells your database which data it should keep top of mind so it’s faster to receive. This is in my opinion the most overseen problem when dealing with big datasets. And why it’s easily overseen is easy to explain. We mostly develop new features with smaller datasets, with not that than much related data. When going in production and when datasets grow this is the number 1 speed killer. Slow search queries , bad indexed relationships are big speed killers , try to avoid them.

Eloquent for president !

Eloquent is great , I love eloquent , I adore eloquent ? . But not always. If I need raw nitro speed I mostly use raw query statements on the query builder. This will execute my queries directly against the PDO layer. Data will not be loaded into model per definition. The most common use case for this approach is reporting. Here we combine data from several tables and collect that into arrays to pass to an Excel builder. This can perfectly be done via raw statements with joins return arrays of data. You really have to try it if you need raw speed.

More info on raw query statements can be found here

Gimme all !