Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
I have more than 1000 record and i fetching all record from database in one time with media, category, subcategory and tags relationship.
Its take more than 1 min to fetch all record i want this should be less than 2 or 3 seconds. I used redis but its also slow.
Are these load times locally or from a remote server?
Can you post the query you are running that is slow and the model and migrations files relating to the query.
My hunch is that you are missing some indexes and possibly it’s an issue with your local environment.
Remote Server - 1 Min Approx
Try = api[dot].quotster[dot]in/api/test
public function randomImages()
{
return Cache::remember('images', 60, function(){
return new ApiCollection(Image::all());
});
}
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
use League\Flysystem\FileNotFoundException;
use Spatie\Image\Exceptions\InvalidManipulation;
use Spatie\Image\Manipulations;
use Spatie\MediaLibrary\HasMedia;
use Spatie\MediaLibrary\InteractsWithMedia;
use Spatie\MediaLibrary\MediaCollections\Models\Media;
class Image extends Model implements HasMedia
{
use InteractsWithMedia;
protected $fillable = [
'name',
'body',
'slug',
'category_id',
'sub_category_id'
];
protected $appends = [
'url',
'total_likes',
'current_user_liked',
'sub_category_name',
'watermark_image',
'category_name',
'thumb',
'width',
'height',
'app_image'
];
protected $casts = [
'total_likes' => 'int',
'current_user_liked' => 'boolean',
'sub_category_name' => 'string'
];
public function setNameAttribute($value)
{
$this->attributes['name'] = $value;
$this->attributes['slug'] = $this->uniqueSlug($value);
}
public function uniqueSlug($value) {
$slug = str_slug($value);
if (static::where('slug', $slug)->exists()) {
$slug = $this->incrementSlug($slug);
}
return $slug;
}
public function incrementSlug($slug) {
$original = $slug;
$count = 2;
while (static::where('slug', $slug)->exists()) {
$slug = "{$original}-" . $count++;
}
return $slug;
}
public function clearMediaCollection(string $collectionName = 'default'): HasMedia
{
// TODO: Implement clearMediaCollection() method.
}
public function registerMediaConversions(Media $media = null): void
{
$this->addMediaCollection('images');
// $this->addMediaConversion('4k')->width(3840)->height(2160);
// $this->addMediaConversion('2k')->width(2048)->height(1440);
// $this->addMediaConversion('full-hd')->width(1920)->height(1080);
// $this->addMediaConversion('hd')->width(1280)->height(720);
// $this->addMediaConversion('facebook')->width(1200)->height(628);
// $this->addMediaConversion('instagram-square')->width(1080)->height(1080);
// $this->addMediaConversion('instagram-portrait')->width(1080)->height(1350);
// $this->addMediaConversion('instagram-landscape')->width(1080)->height(608);
$this->addMediaConversion('thumb')->height(400);
$this->addMediaConversion('app')->height(720)->nonOptimized();
$this->addMediaConversion('download-watermark')
->watermark(public_path('/asset/site/watermark.png'))
->watermarkWidth(150)
->watermarkPosition(Manipulations::POSITION_BOTTOM_RIGHT);
}
public function getUrlAttribute()
{
$media = $this->getMedia('images')->first();
return $media != null ? $media->getUrl() :"";
}
public function getThumbAttribute()
{
$media = $this->getMedia('images')->first();
return $media != null ? $media->getUrl('thumb') : "";
}
public function getAppImageAttribute()
{
$media = $this->getMedia('images')->first();
return $media != null ? $media->getUrl('app') : "";
}
public function getWidthAttribute()
{
if($this->thumb){
list($width) = getimagesize($this->thumb);
return $width;
}
}
public function getHeightAttribute()
{
if($this->thumb){
list($height) = getimagesize($this->thumb);
return $height;
}
}
public function getConversionsAttribute()
{
$media_urls = [];
$media = $this->getMedia('images');
foreach ($media[0][ "custom_properties"]["generated_conversions"] as $conversion => $hasConversion){
if($hasConversion === true && $conversion !== 'download-watermark'){
$conversionName = ucwords(str_replace('-', ' ', $conversion));
$array = [
'display_name' => $conversionName,
'conversion_name' => $conversion,
'url' => $media[0]->getUrl($conversion)
];
array_push($media_urls, $array);
}
}
return $media_urls;
}
public function getTotalLikesAttribute(){
return $this->likes()->count();
}
public function getCurrentUserLikedAttribute()
{
return !!array_filter( $this->likes->toArray(), function($like){
return $like['ip'] == $_SERVER['REMOTE_ADDR'];
});
}
public function getSubCategoryNameAttribute()
{
return $this->subCategory->name ?? "";
}
public function getCategoryNameAttribute()
{
return $this->subCategory->category->name ?? "";
}
public function getWatermarkImageAttribute()
{
$media = $this->getMedia('images')->first();
return $media != null ? $media->getUrl('download-watermark') : "";
}
//relationship
public function likes(){
return $this->hasMany(Like::class);
}
public function tags()
{
return $this->belongsToMany(Tag::class)->withTimestamps();
}
public function category()
{
return $this->belongsTo(Category::class);
}
public function subCategory()
{
return $this->belongsTo(SubCategory::class);
}
}
Are you doing image processing as part of this request too or is it just lots of activity on the database?
If you view the network tab in console can you check how long it takes for the server to respond before it starts returning its data?
What sort of response times do you get if you pull 100 rather than 1000?
What’s the benefit of getting 1000 rows all at once? Wouldn’t paging 50 or 100 at a time speed things up?
I agree with @automica I had this same issue, however, for me, grabbing 1k records from the backend wasn't the problem, it was rendering all the data on the DOM, which took time for the HTML to generate the div/table/rows etc... thus why the network tab in console would allow you to discover this...
So yes, Pagination helps here.
However, if the data isn't changing often, you can store the data on stored cache, but you lose some power of searching. It's a Give and Take...
So, my understanding is you're fetching 1000 records as a single object, correct? Is this a GET request that renders back to the DOM?
Just a wild idea, I see in the Image::all(), you're returning ALL the images, however, why not use paginate() Image::paginate(15), then process those first, after, grab the next 15. Inside the collections, give you the information needed to grab the next set of records...
App link = https://play.google.com/store/apps/details?id=com.quotster.quotster
Im using all records here so i cant paginate data. I need all record as much as possible.
Are you storing those images in the database as blobs or are you storing them on a filesystem?
@tray2 in file storage
@kkhicher1 install debugbar and set up some slow query logging. 2-3 seconds to query 10 records is slow so perhaps you have some n+1 problem going on or you need to add some indexing or eager loading.
What sort of spec is your remote server? Does it have a decent amount of RAM?
@automica digital ocean 10 dollar plan 2gb ram 1cpu ... currently no visitor
Can you try adding
protected $with = [];
With the relationships you are referencing to eager load them?
You are chaining a lot of models in your accessors so this should hopefully speed some of that up
Let me try
i want to remove category, media, likes, sub_category from my api record. anyhow
{
"id": 14,
"name": "Beleive in yourself",
"slug": "beleive-in-yourself",
"body": "BELIEVE YOU CAN AND YOU'RE HALFWAY THERE",
"total_downloads": 0,
"category_id": 3,
"sub_category_id": "4",
"created_at": "2020-10-05T16:13:16.000000Z",
"updated_at": "2020-10-05T16:13:16.000000Z",
"url": "http://api.quotster.in/storage/34/BELIEVE-YOU-CAN-AND-YOU'RE-HALFWAY-THERE.png",
"total_likes": 13,
"current_user_liked": false,
"sub_category_name": "Quotes",
"watermark_image": "http://api.quotster.in/storage/34/conversions/BELIEVE-YOU-CAN-AND-YOU'RE-HALFWAY-THERE-download-watermark.jpg",
"category_name": "Motivational Quotes",
"thumb": "http://api.quotster.in/storage/34/conversions/BELIEVE-YOU-CAN-AND-YOU'RE-HALFWAY-THERE-thumb.jpg",
"width": 400,
"height": 400,
"app_image": "http://api.quotster.in/storage/34/conversions/BELIEVE-YOU-CAN-AND-YOU'RE-HALFWAY-THERE-app.jpg",
"media": [
{
"id": 34,
"model_type": "App\Image",
"model_id": 14,
"uuid": "7731c88d-4d30-4da4-a61e-e0f43b33ec14",
"collection_name": "images",
"name": "BELIEVE YOU CAN AND YOU'RE HALFWAY THERE",
"file_name": "BELIEVE-YOU-CAN-AND-YOU'RE-HALFWAY-THERE.png",
"mime_type": "image/png",
"disk": "public",
"conversions_disk": "public",
"size": 3619713,
"manipulations": [],
"custom_properties": {
"generated_conversions": {
"app": true,
"thumb": true,
"download-watermark": true
}
},
"responsive_images": [],
"order_column": 22,
"created_at": "2020-10-05T16:13:16.000000Z",
"updated_at": "2020-10-30T04:19:29.000000Z"
}
],
"likes": [
{
"id": 25,
"ip": "172.68.146.68",
"image_id": 14,
"created_at": "2020-10-10T16:51:48.000000Z",
"updated_at": "2020-10-10T16:51:48.000000Z"
},
{
"id": 35,
"ip": "172.68.146.80",
"image_id": 14,
"created_at": "2020-10-11T04:02:49.000000Z",
"updated_at": "2020-10-11T04:02:49.000000Z"
},
{
"id": 81,
"ip": "162.158.165.140",
"image_id": 14,
"created_at": "2020-10-11T10:13:51.000000Z",
"updated_at": "2020-10-11T10:13:51.000000Z"
},
{
"id": 82,
"ip": "172.68.146.248",
"image_id": 14,
"created_at": "2020-10-11T10:13:53.000000Z",
"updated_at": "2020-10-11T10:13:53.000000Z"
},
{
"id": 83,
"ip": "172.69.135.226",
"image_id": 14,
"created_at": "2020-10-11T10:13:54.000000Z",
"updated_at": "2020-10-11T10:13:54.000000Z"
},
{
"id": 84,
"ip": "172.69.135.70",
"image_id": 14,
"created_at": "2020-10-11T10:13:56.000000Z",
"updated_at": "2020-10-11T10:13:56.000000Z"
},
{
"id": 86,
"ip": "162.158.167.34",
"image_id": 14,
"created_at": "2020-10-11T10:13:59.000000Z",
"updated_at": "2020-10-11T10:13:59.000000Z"
},
{
"id": 87,
"ip": "162.158.166.71",
"image_id": 14,
"created_at": "2020-10-11T10:14:00.000000Z",
"updated_at": "2020-10-11T10:14:00.000000Z"
},
{
"id": 135,
"ip": "162.158.165.76",
"image_id": 14,
"created_at": "2020-10-11T14:58:05.000000Z",
"updated_at": "2020-10-11T14:58:05.000000Z"
},
{
"id": 200,
"ip": "162.158.165.150",
"image_id": 14,
"created_at": "2020-10-31T14:24:26.000000Z",
"updated_at": "2020-10-31T14:24:26.000000Z"
},
{
"id": 214,
"ip": "172.68.146.224",
"image_id": 14,
"created_at": "2020-11-03T02:16:39.000000Z",
"updated_at": "2020-11-03T02:16:39.000000Z"
},
{
"id": 262,
"ip": "162.158.167.148",
"image_id": 14,
"created_at": "2020-11-15T02:45:34.000000Z",
"updated_at": "2020-11-15T02:45:34.000000Z"
},
{
"id": 263,
"ip": "172.68.146.146",
"image_id": 14,
"created_at": "2020-11-16T02:50:21.000000Z",
"updated_at": "2020-11-16T02:50:21.000000Z"
}
],
"sub_category": {
"id": 4,
"category_id": 3,
"name": "Quotes",
"slug": "quotes",
"created_at": "2020-10-01T11:07:55.000000Z",
"updated_at": "2020-10-05T15:28:13.000000Z",
"category": {
"id": 3,
"name": "Motivational Quotes",
"slug": "motivational-quotes",
"created_at": "2020-10-01T10:09:27.000000Z",
"updated_at": "2020-10-03T04:57:11.000000Z",
"url": "http://api.quotster.in/storage/131/Motivational-Quotes",
"media": [
{
"id": 131,
"model_type": "App\Category",
"model_id": 3,
"uuid": "5c3e42a5-717c-42e4-a496-576f2c0b81d0",
"collection_name": "categories",
"name": "Motivational Quotes",
"file_name": "Motivational-Quotes",
"mime_type": "image/jpeg",
"disk": "public",
"conversions_disk": "public",
"size": 86379,
"manipulations": [],
"custom_properties": [],
"responsive_images": [],
"order_column": 89,
"created_at": "2020-10-29T15:59:17.000000Z",
"updated_at": "2020-10-29T15:59:17.000000Z"
}
]
}
}
}
@kkhicher1 what do you mean by 'remove'? are you looking to remove accessors and related models?
if so you could add a select() in your query and add only the fields only you want.
BTW how did the eager loading work? did that speed anything up?
@automica i think likes table make query slow
Please or to participate in this conversation.