Eloquent trick too long with remote MySQL database

Question

I am developing a website (Laravel) and iOS application that consumes the REST API, which I also develop using Laravel. API queries from a MySQL database hosted on an AWS RDS instance.

When I configured the API, as well as localhost applications and APIs pointing to my local database (the same machine as the applications and APIs), it works fine as expected, but the problem is when I configure the API to query from an AWS RDS database instead of locally. Simple queries made with an eloquent example

Product::where('brand_id', '=', $id) ->get() 

takes 10-15 minutes, but if I use the query builder, for example,

 DB::select('select * from products where brand_id = ?', [$id]); 

works great.

Executing a query directly in MySQL or launching the product :: all () in Laravel Tinker works as intended with the local API / Remote DB setting.

EDIT:

This is what I get when I do SHOW CREATE TABLE products

 CREATE TABLE `products` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `SKU` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `price` double(8,2) NOT NULL, `description` text COLLATE utf8mb4_unicode_ci NOT NULL, `product_details` text COLLATE utf8mb4_unicode_ci NOT NULL, `material_and_care` text COLLATE utf8mb4_unicode_ci NOT NULL, `material` text COLLATE utf8mb4_unicode_ci NOT NULL, `care` text COLLATE utf8mb4_unicode_ci NOT NULL, `colors` text COLLATE utf8mb4_unicode_ci NOT NULL, `made_in` text COLLATE utf8mb4_unicode_ci NOT NULL, `size_chart` text COLLATE utf8mb4_unicode_ci, `size_chart_url` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `brand_id` int(10) unsigned NOT NULL, `category_id` int(10) unsigned NOT NULL, `published` tinyint(1) NOT NULL DEFAULT '0', `featured` tinyint(1) NOT NULL DEFAULT '0', `promo_title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `promo_caption` text COLLATE utf8mb4_unicode_ci NOT NULL, `shipping_height` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `shipping_width` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `shipping_length` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `shipping_weight` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `is_vertical` tinyint(1) NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `deleted_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `products_category_id_foreign` (`category_id`), KEY `products_brand_id_foreign` (`brand_id`), CONSTRAINT `products_brand_id_foreign` FOREIGN KEY (`brand_id`) REFERENCES `brands` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `products_category_id_foreign` FOREIGN KEY (`category_id`) REFERENCES `subcategories` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=501 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 

My product model:

 class Product extends Model { use SoftDeletes, CascadeSoftDeletes; protected $cascadeDeletes = [ 'items', 'options', 'images', 'sizes' ]; protected $fillable = [ 'sku', 'name', 'price', 'description', 'product_details', 'material_and_care', 'material', 'color_1', 'color_2', 'color_3', 'care', 'made_in', 'size_chart', 'size_chart_url', 'brand_id', 'category_id', 'published', 'featured', 'promo_title', 'promo_caption', 'shipping_weight', 'shipping_height', 'shipping_width', 'shipping_length', 'is_vertical' ]; protected $appends = [ 'interests', 'brand', 'options', 'sizes', 'items', 'images', 'comment_count', 'discount', 'suits_me_count' ]; protected $hidden = [ 'created_at', 'deleted_at', 'updated_at', 'subcategory' ]; protected static function boot() { parent::boot(); static::addGlobalScope(new PublishedProductScope); } public function getDiscountAttribute() { return $this->discount()->first(); } public function getSuitsMeCountAttribute() { return $this->suitsmes()->count(); } public function getCommentCountAttribute() { return $this->comments()->count(); } public function getImagesAttribute(){ return $this->images()->pluck("url"); } public function getInterestsAttribute() { return $this->interests()->get(); } public function getBrandAttribute(){ return $this->brand()->first(); } public function getOptionsAttribute(){ return $this->options()->get(); } public function getSizesAttribute(){ return $this->sizes()->get(); } public function getItemsAttribute(){ return $this->items()->get(); } public function interests() { return $this->belongsToMany('App\Interest', 'product_interest'); } public function brand() { return $this->belongsTo('App\Brand'); } public function options() { return $this->hasMany('App\ProductOption'); } public function sizes() { return $this->hasMany('App\ProductSize'); } public function items() { return $this->hasMany('App\ProductItem'); } public function images() { return $this->hasMany('App\ProductImage'); } public function comments() { return $this->hasMany('App\ProductComment'); } public function suitsmes() { return $this->belongsToMany('App\User', 'wishlist', 'product_id', 'user_id'); } public function discount(){ return $this->hasOne('App\Discount'); } public function category() { return $this->belongsTo('App\Subcategory', 'category_id'); } } 

And my brand model:

 class Brand extends Model { protected $fillable = [ 'first_name', 'last_name', 'email', 'phone_number', 'birth_date', 'ssn', 'street_address', 'street_address_2', 'city_address', 'state_address', 'postal_code_address', 'legal_name', 'dba', 'tax_id', 'street_business_address', 'street_business_address_2', 'city_business_address', 'state_business_address', 'postal_code_business_address', 'destination_fund', 'email_business', 'phone_number_business', 'account_number_fund', 'routing_number_fund' ]; protected $hidden = [ 'created_at', 'deleted_at', 'updated_at' ]; protected $appends = [ 'images' ]; public function getImagesAttribute() { return $this->images()->get(); } public function getBillboardPicturesAttribute() { $pictures = [$this->billboard, $this->billboard2, $this->billboard3, $this->billboard4]; return $pictures; } public function users() { return $this->belongsToMany('App\User', 'user_brand_role', 'brand_id', 'user_id'); } public function getInterestsAttribute() { return $this->interests()->pluck('interest_id'); } public function interests() { return $this->belongsToMany('App\Interest', 'brand_interest', 'brand_id', 'interest_id'); } public function products() { return $this->hasMany('App\Product'); } public function images() { return $this->hasMany('App\BrandImage'); } public function categories() { return $this->hasMany('App\Category'); } public function getCatalogAttribute() { return $this->categories()->orderBy('name', 'asc')->get(); } } 

Answer 1

The problem is this piece of code in your Product model:

 protected $appends = [ 'interests', 'brand', 'options', 'sizes', 'items', 'images', 'comment_count', 'discount', 'suits_me_count' ]; 

These properties are relationships, and therefore they result in multiple joins each time Eloquent reads / returns a row.

This query: DB::select('select * from products where brand_id = ?', [$id]); does not lead to any joins and represents a query of a choice from one table. Therefore, when you compare the results, you will find a significant difference in query time.

There are 2 possible solutions:

1. Delete add

Remove the additions and whenever you need a relationship, use the with function

2. Make hidden app properties

You can add this code to your Product model:

 protected $hidden = [ 'created_at', 'deleted_at', 'updated_at', 'interests', 'brand', 'options', 'sizes', 'items', 'images', 'comment_count', 'discount', 'suits_me_count' ]; 

When you need a property, just use $product->makeVisible('property_name') . Otherwise, by default, these properties will not be loaded or requested.