Wednesday, March 1, 2017

How to improve database performance on Laravel Eloquent handy appends?

So Laravel's $appends is a very nice feature. I've got a products table, I added vendor, images and others to the $appends array, so my code looks like this:

class Product extends \ResourceModel {

protected $appends = ['vendor', 'images'];

function getVendorAttribute() {
    return $this->vendor()->first();
}

function getImages() {
    $this->_images = $this->images()->get();
}

Neat huh?

But, not so fas, here's the list of queries that this neat piece of code is executing:

select * from `products`;
select * from `images` where `images`.`imageable_id` = 1 and `images`.`imageable_type` = 'Product';
select * from `vendors` where `vendors`.`id` = 1;
select * from `images` where `images`.`imageable_id` = 2 and `images`.`imageable_type` = 'Product';
select * from `vendors` where `vendors`.`id` = 2;
select * from `images` where `images`.`imageable_id` = 3 and `images`.`imageable_type` = 'Product';
select * from `vendors` where `vendors`.`id` = 3;
select * from `images` where `images`.`imageable_id` = 4 and `images`.`imageable_type` = 'Product';
select * from `vendors` where `vendors`.`id` = 4;
select * from `images` where `images`.`imageable_id` = 5 and `images`.`imageable_type` = 'Product';
select * from `vendors` where `vendors`.`id` = 5;

Of course, I rather be doing:

select * from `products`;
select * from `images` where `images`.`imageable_id` in (1, 2, 3, 4, 5) and `images`.`imageable_type` = 'Product';
select * from `vendors` where `vendors`.`id` in(1, 2, 3, 4, 5);

Or even the JOIN equivalent.

So, my questions in detail:

1) I'm loading products in pages of 20. This is generating around 41 queries (more actually, I simplified it for the sake of this question). I think this is really harming performance, am I right? 41 simple queries of the first type are indeed more time consuming for an average MySQL box than 3 queries of the second type?

2) Is there a bundled (or packaged, or simple) way of doing this on Laravel before I go mad fixing all my app logic?

Thanks in advance!




via Mauro

Advertisement