256 Kilobytes

Answers in Frameworks | By August R. Garcia

Published | Last Update

109 views, 1 RAM, and 0 comments

Tags: Laravel, Debugging, Eloquent, Database Queries

Profile Photo - August R. GarciaAugust R. GarciaLARPing as a Sysadmi...Portland, ORSite Owner

Rework/expansion of a solution provided in a Stack Overflow thread from 2 years, 11 months ago (December 2016).

My team and I are working on a rather big project. There's queries going on everywhere - in controllers, in view composers in views (lazy loading) and probably in some other services as well. It's getting hard to keep a track of it all and the page load speed is fairly slow at the moment.

Where would I put \DB::enableQueryLog() and \DB::getQueryLog() to log ALL the queries and dump them? Basically I'm looking for some place in code that happens before any of the queries happen (to put enableQueryLog()) and I'm looking for a place that happens after the views render (to dump getQueryLog()).

What would be a good way to go about this?

Thanks in advance.

Source: Laravel 5.3 - How to log all queries on a page?

The solution in that thread works correctly:

Here comes the perfect example:

https://laravel.com/docs/5.3/database#listening-for-query-events

Open app\Providers\AppServiceProvider.php and add the following to Boot() function:

DB::listen(function ($query) {
    var_dump([
        $query->sql,
        $query->bindings,
        $query->time
    ]);
});

Source: Hudson Pereira via Stack Overflow

However, this tends to:

  • Be fairly verbose, since tons of information is being dumped out for each query; and 
  • Can be hard to identify which queries are the most important bottlenecks to optimize.

Updated this code as shown below to instead show color-coded boxes for each query showing query execution time in milliseconds with more information on mouseover/hover.

<?php

namespace App\Providers;

use Illuminate\Support\ServiceProvider;
use Schema;

class AppServiceProvider extends ServiceProvider
{
    /**
     * Bootstrap any application services.
     *
     * @return void
     */  
    public function boot()
    {
        Schema::defaultStringLength(191);

        // This IF block is the actual code that is added to this file. 
        // Either change this to FALSE to disable and/or add some other check such as 
        // "if on localhost," "if user's ID/IP is your ID/IP," etcetera 
        if (TRUE) {
                $GLOBALS['num_queries'] = 0;
                \DB::listen(function ($query) {
                        ob_start();
                        var_dump([
                            $query->sql,
                            $query->bindings,
                            $query->time
                        ]);
                        $result = ob_get_clean();
        
                        // Execution time in milliseconds       
                        $time = $query->time; 

                        // Color code query execution times by how long they took 
                        if     ($time > 10)  $color = "background-color:red;"         ;
                        elseif ($time > 3)   $color = "background-color:orange;"      ;
                        elseif ($time > 1)   $color = "background-color:tomato;"      ;
                        elseif ($time > 0.5) $color = "background-color:lightsalmon;" ;
                        elseif ($time > 0.1) $color = "background-color:yellow;"      ;
                        else                 $color = "background-color:lightyellow;" ;
                        
                        // Print out the color-coded debugging statement
                        // Mouseover/hover to show the title="" tooltip with 
                        // the actual SQL query that was run                  
                        echo "<abbr style='color:black;$color font-weight:bold;padding:.25em;border:1px solid black;margin:.25em;' "
                             ."title=\"".htmlspecialchars($result)."\">float($time)</abbr>";
                });
        }       
    }   
        
    /**
     * Register any application services.
     *
     * @return void
     */
    public function register()
    {
        //
    }
}

Example of output (from localhost; obviously don't run this on a production site, or at least add an IF statement to only show to your account/IP/other) is shown in the cover image for this post. Note that the boxes will appear where the query was made in the page's execution. Queries in controllers and so on will be at the very top of the page; if there are queries being made in views/templates or elsewhere, they'll appear at that point in the page.

Users Who Have Downloaded More RAM:
Hash Brown (5 months ago)
🐏 ⨉ 1
Posted by August R. Garcia 5 months ago

Edit History

• [2019-12-12 21:14 PST] August R. Garcia (5 months ago)
🕓 Posted at 12 December, 2019 21:14 PM PST

Sir, I can do you a nice SEO.

Post a New Comment

Do you like having a good time?

Register an Account

You can also login to an existing account or reset your password. All use of this site is subject to the terms of service and privacy policy.

Read Quality Articles

Read some quality articles. If you can manage to not get banned for like five minutes, you can even post your own articles.

View Articles →

Argue with People on the Internet

Use your account to explain why people are wrong on the Internet forum.

View Forum →

Vandalize the Wiki

Or don't. I'm not your dad.

View Wiki →

Ask and/or Answer Questions

If someone asks a terrible question, post a LMGTFY link.

View Answers →

Make Some Money

Hire freelancers and/or advertise your goods and/or services. Hire people directly. We're not a middleman or your dad. Manage your own business transactions.

Register an Account

Answers— Read More

Find more related content below!