Indexes are important part in database. Indexes are used to\nquickly retrieve data without having to search every row in a database table is\naccessed. The users cannot see the indexes, they are just used to speed up\nsearches\/queries.<\/p>\n\n\n\n
How to use? For example, you have thousands of rows of users and you want to get an email.<\/p>\n\n\n\n
Select * from users where email = \u201cme@gmail.com\u201d<\/code><\/pre>\n\n\n\nWithout index, this process will be from top to bottom until\nit the email is found.<\/p>\n\n\n\n
Using with index, in Laravel, the easy way is to create a migration file.<\/p>\n\n\n\n
public function up()\n{\n Schema::table(\u2018users\u2019, function(Blueprint $table)\n {\n $table->index(\u2018email\u2019);\n });\n}<\/code><\/pre>\n\n\n\nOr if you currently on fresh project just add index to column you want to<\/p>\n\n\n\n
public function up()\n{\n Schema::create(\u2018users, function(Blueprint $table) {\n \u2026\n $table->index(\u2018email\u2019);\n \u2026\n });\n}<\/code><\/pre>\n\n\n\nIndex is not only for single column. You can use multiple column like this $table->index([\u2018email\u2019, \u2018 phone_no\u2019]);<\/strong> Let\u2019s us look at a query that could use two different indexes on the table based on the WHERE <\/strong>clause restrictions. Multi column indexes are called compound indexes<\/strong>.<\/p>\n\n\n\nEloquent Eager Loading<\/strong><\/p>\n\n\n\nAt its core Eager Loading, is telling Eloquent that you want to grab a model with <\/strong>specific relationships that way the framework produces a more performant query to grab all the data you will need. By eager loading, you can take many queries down to just one or two. Wrong implementation cause N+1. Here is where common developer do which cause N+1.<\/p>\n\n\n\nFor example:<\/p>\n\n\n\n
Class Post extends Model\n{\n public function author()\n {\n return $this->belongsTo(Author::class);\n }\n}<\/code><\/pre>\n\n\n\nLets fetch author about 100 rows\nfrom the table and what happen<\/p>\n\n\n\n
Mistake 1<\/strong><\/p>\n\n\n\n$post = Post::all();\nforeach($posts as $post) \n{\n $total_user = $post->author;\n}<\/code><\/pre>\n\n\n\nThe query runs once to get all the author and for each loop it query another query for author for 100 times. 100 + 1 = N+1 problem. Imagine if you want to fetch thousand to data. If one query cost 100ms * 100 = 10,000ms it takes to complete process.<\/p>\n\n\n\n
Solution<\/strong><\/p>\n\n\n\nIt can be solve by using with method in Eager loading. For example<\/p>\n\n\n\n
$post = App\\Post::with(\u2018author\u2019)->get();<\/code><\/pre>\n\n\n\nIf you had multiple related associations, you can eager load them with an array:<\/p>\n\n\n\n
$post = App\\Post::with([\u2018author\u2019, \u2018comments\u2019])->get();\n$post =App\\Post::with(\u2018author.profile\u2019)->get();<\/code><\/pre>\n\n\n\nWith this, instead of hitting database N+1, with will single query by joining those table and transform it into collection. So you can freely access the collection object without hitting the database. <\/p>\n\n\n\n
By Ami<\/p>\n
\n <\/g:plusone><\/div>\n
![\"\u3053\u306e\u30a8\u30f3\u30c8\u30ea\u30fc\u3092\u306f\u3066\u306a\u30d6\u30c3\u30af\u30de\u30fc\u30af\u306b\u8ffd\u52a0\"](\"\/\/b.hatena.ne.jp\/images\/entry-button\/button-only@2x.png\")
<\/a>