Hey! Laravelians ❤️ Can You Tell Me?
Did you ever have worked on a very huge Laravel project with millions of dataset & how do you optimize your queries in such a project using Eloquent or Raw SQL?
You can certainly use Eloquent to query millions of records. However, my advice is not to use models for querying.
For example, instead of doing:
$posts = Post::whereHas('tags', function($query){
$query->whereIn('tag',['php','laravel']);
})->get();
use joins:
$posts = DB::table('posts')
->join('post_tag','posts.id','=','post_tag.post_id')
->join('tags','post_tag.tag_id','=','tags.id')
->whereIn('tags.tag',['php','laravel'])
->get('posts.*');
It's true that this way the result is not a collection of models, so you won't have access to accessors and model methods, but the query will be several orders of magnitude faster. When we're talking about hundreds of thousands or millions of records, this makes a difference.
Moreover, even when using Chunks or lazy collections, you'll still have significant savings in terms of memory and time because models won't be instantiated.
With millions of records in my opinion joins should not be used, we should denormalise data and use indexes to get records in a separate query instead, querying performance will be better, also you can partition better when needed.
Joins are never recommended on very large data set. Also consider caching frequently queried data and save pre calculated results and fetch from there.
Fair point, but I think that depends on a per case basis: denormalization could introduce complexity managing data as you must ensure data integrity manually, unless you need data only for reports or similar, in this case you can easily export a denormalized snapshot.
My point responding to @muhammadabdullahmirza was only to highlight the performance difference (on speed and memory efficency) between using Models (with whereHas) and Eloquent query (with join), database normalization is more a database design and optimization topic.
