{"id":28771,"date":"2024-04-15T06:56:47","date_gmt":"2024-04-15T05:56:47","guid":{"rendered":"https:\/\/nelkodev.com\/blog\/optimizando-consultas-sql-en-laravel-guia-completa-con-eloquent\/"},"modified":"2024-06-03T18:40:53","modified_gmt":"2024-06-03T17:40:53","slug":"optimizando-consultas-sql-en-laravel-guia-completa-con-eloquent","status":"publish","type":"post","link":"https:\/\/nelkodev.com\/en\/php\/optimizing-sql-queries-in-laravel-complete-guide-with-eloquent\/","title":{"rendered":"Optimizing SQL Queries in Laravel: Complete Guide with Eloquent"},"content":{"rendered":"<p>Laravel is a PHP framework highly appreciated for its elegance and ability to handle projects of all sizes, from small websites to large enterprise applications. One of Laravel&#039;s strengths is its Eloquent ORM (Object-Relational Mapping), which simplifies interaction with databases through an object-oriented programming model. Although Eloquent simplifies database operations, they can be inefficient if not properly optimized. This article delves into effective techniques for optimizing SQL queries in Laravel, ensuring your application performs optimally.<\/p>\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_80 counter-hierarchy ez-toc-counter ez-toc-custom ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #ffffff;color:#ffffff\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewbox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #ffffff;color:#ffffff\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewbox=\"0 0 24 24\" version=\"1.2\" baseprofile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/nelkodev.com\/en\/php\/optimizing-sql-queries-in-laravel-complete-guide-with-eloquent\/#Entendiendo_Eloquent_ORM_y_los_Problemas_de_Rendimiento_Comunes\" >Understanding Eloquent ORM and Common Performance Issues<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/nelkodev.com\/en\/php\/optimizing-sql-queries-in-laravel-complete-guide-with-eloquent\/#Evitando_Consultas_N1\" >Avoiding N+1 Queries<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/nelkodev.com\/en\/php\/optimizing-sql-queries-in-laravel-complete-guide-with-eloquent\/#Solucion_Carga_Eager\" >Solution: Eager Load<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/nelkodev.com\/en\/php\/optimizing-sql-queries-in-laravel-complete-guide-with-eloquent\/#Optimizacion_de_la_Seleccion_de_Campos\" >Field Selection Optimization<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/nelkodev.com\/en\/php\/optimizing-sql-queries-in-laravel-complete-guide-with-eloquent\/#Uso_de_Indices_en_la_Base_de_Datos\" >Use of Indexes in the Database<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/nelkodev.com\/en\/php\/optimizing-sql-queries-in-laravel-complete-guide-with-eloquent\/#Reduccion_de_Cargas_con_Tecnicas_Avanzadas\" >Load Reduction with Advanced Techniques<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/nelkodev.com\/en\/php\/optimizing-sql-queries-in-laravel-complete-guide-with-eloquent\/#Subconsultas_y_Consultas_Existenciales\" >Subqueries and Existential Queries<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/nelkodev.com\/en\/php\/optimizing-sql-queries-in-laravel-complete-guide-with-eloquent\/#Paginacion_Eficaz\" >Effective Pagination<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-9\" href=\"https:\/\/nelkodev.com\/en\/php\/optimizing-sql-queries-in-laravel-complete-guide-with-eloquent\/#Monitoreo_y_Optimizacion_Continua\" >Continuous Monitoring and Optimization<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-10\" href=\"https:\/\/nelkodev.com\/en\/php\/optimizing-sql-queries-in-laravel-complete-guide-with-eloquent\/#Conclusion\" >Conclusion<\/a><\/li><\/ul><\/nav><\/div>\n<h2><span class=\"ez-toc-section\" id=\"Entendiendo_Eloquent_ORM_y_los_Problemas_de_Rendimiento_Comunes\"><\/span>Understanding Eloquent ORM and Common Performance Issues<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Eloquent allows you to work with the database using PHP models that represent tables in the database. Although this is extremely useful, it can lead to performance issues if not handled carefully. N+1 queries, unnecessary data loads and inappropriate use of indexes are some of the common problems.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Evitando_Consultas_N1\"><\/span>Avoiding N+1 Queries<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>One of the most frequent errors in Laravel applications is the N+1 query problem. It happens when a query is run to get the parent records, and for each record, a new query is run to get related information.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Solucion_Carga_Eager\"><\/span>Solution: Eager Load<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>The eager load (<code>eager loading<\/code>) is a technique to solve this problem. Eloquent allows you to preload the relationships you need when performing the initial query using the method <code>with()<\/code>.<\/p>\n<pre><code class=\"&quot;language-php&quot;\">$users = User::with(&#039;posts&#039;)-&gt;get();<\/code><\/pre>\n<p>With the above code, Laravel will perform one query to fetch all users and an additional query to fetch all posts related to those users, eliminating the problem of multiple queries.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Optimizacion_de_la_Seleccion_de_Campos\"><\/span>Field Selection Optimization<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Loading more data than necessary is a common mistake. Use the method <code>select<\/code> to specify only the fields you really need.<\/p>\n<pre><code class=\"&quot;language-php&quot;\">$users = User::select(&#039;id&#039;, &#039;name&#039;, &#039;email&#039;)-&gt;get();<\/code><\/pre>\n<h2><span class=\"ez-toc-section\" id=\"Uso_de_Indices_en_la_Base_de_Datos\"><\/span>Use of Indexes in the Database<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>It is crucial that columns that are frequently used in WHERE, ORDER BY clauses and JOINs are indexed. Laravel does not automatically handle indexing, so you must manually review and optimize your indexes in the database.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Reduccion_de_Cargas_con_Tecnicas_Avanzadas\"><\/span>Load Reduction with Advanced Techniques<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<h3><span class=\"ez-toc-section\" id=\"Subconsultas_y_Consultas_Existenciales\"><\/span>Subqueries and Existential Queries<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Subqueries can be powerful for filtering data without needing to bring it all into your application. Laravel Eloquent allows you to use methods like <code>whereExists<\/code> to optimize operations that involve the existence of linked records.<\/p>\n<pre><code class=\"&quot;language-php&quot;\">$USERS = User :: WHEREEXISTS (FUNCTION ($QUERY) {$QUERY-&gt; SELECT (DB :: RAW (1)) -&gt; FROM (&amp; #039; POSTS &amp; #039;) -&gt; WHERE #039; USERS.ID &amp; #039 ;); })-&gt;get();<\/code><\/pre>\n<h3><span class=\"ez-toc-section\" id=\"Paginacion_Eficaz\"><\/span>Effective Pagination<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Paging is crucial to limit the number of records loaded into memory and transferred to the client application. Laravel makes it easy to paginate results by simply adding the method <code>paginate()<\/code>.<\/p>\n<pre><code class=\"&quot;language-php&quot;\">$users = User::paginate(10);<\/code><\/pre>\n<p>This code will page the result set and only 10 users will be loaded per page, improving efficiency in terms of memory and loading speed.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Monitoreo_y_Optimizacion_Continua\"><\/span>Continuous Monitoring and Optimization<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Optimization does not end with application deployment. It is essential to continually monitor performance and adjust queries and configurations based on actual usage data. Tools like Laravel Telescope provide a detailed viewer of query behavior, which can be invaluable in identifying bottlenecks.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Conclusion\"><\/span>Conclusion<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Taking full advantage of Eloquent&#039;s capabilities in Laravel to optimize SQL queries requires a deep understanding of both the ORM and the quirks of the underlying database system. Applying eager loads, selecting only necessary fields, using effective indexing, choosing advanced techniques such as subqueries, and managing pagination correctly are key practices to improve performance.<\/p>\n<p>For more information about Laravel and development best practices, visit <a href=\"https:\/\/nelkodev.com\/en\/\">NelkoDev<\/a>. Do you have specific questions or need help with your project? Do not doubt <a href=\"https:\/\/nelkodev.com\/en\/contact\/\">contact me<\/a>. I&#039;m here to help you take your projects to the next level!<\/p>","protected":false},"excerpt":{"rendered":"<p>Laravel is a PHP framework highly appreciated for its elegance and ability to handle projects of all sizes, from small websites to large enterprise applications. One of Laravel\u2019s strong points is its Eloquent ORM (Object-Relational Mapping), which simplifies interaction with databases through a user-oriented programming model.<\/p>","protected":false},"author":1,"featured_media":28772,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2206,420,1890],"tags":[205,500,90,1516,1937,358,229,851,15,1008,202,504],"class_list":["post-28771","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-php","category-blog","category-pruebas-de-rendimiento","tag-blog","tag-completa","tag-con","tag-consultas","tag-eloquent","tag-guia","tag-laravel","tag-optimizando","tag-php","tag-pruebas","tag-rendimiento","tag-sql"],"_links":{"self":[{"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/posts\/28771","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/comments?post=28771"}],"version-history":[{"count":0,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/posts\/28771\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/media\/28772"}],"wp:attachment":[{"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/media?parent=28771"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/categories?post=28771"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/tags?post=28771"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}