Laravel Slow queries

Question

public function delete( ReportDetailRequest $request )
    {
        $id = (int)$request->id;
        $customerRecord = CustomerInfo::find($id);
        $customerRecord->delete();
    }

I currently have the above in a laravel application where a DELETE request is sent to this controller. At the moment, as you can see, its very simple, but the query seems super slow. It comes back in postman in 2.23 seconds. What should I try to speed this up? The database layer (mysql) does have an index on ID from what I can tell and the application isn't running in debug. Is this typical?

edit: Good thinking that the request validation may be doing something (it is validating that this user has auth to delete).

class ReportDetailRequest extends FormRequest
{
    /**
     * Determine if the user is authorized to make this request.
     *
     * @return bool
     */
    public function authorize()
    {
        $id = (int)$this->route('id');
        $customerInfo = CustomerInfo::find($id)->first();
        $company = $customerInfo->company_id;
        return (auth()->user()->company->id  == $company );
    }

    /**
     * Get the validation rules that apply to the request.
     *
     * @return array
     */
    public function rules()
    {
        return [
            //
        ];
    }
}

Show create table:

CREATE TABLE "customer_info" (
  "id" int(11) NOT NULL AUTO_INCREMENT,
  "user_id" int(11) DEFAULT NULL,
  "report_guid" varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  "customer_email" varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  "created_at" timestamp NULL DEFAULT NULL,
  "updated_at" timestamp NULL DEFAULT NULL,
  "report_read" tinyint(1) NOT NULL,
  "customer_name" varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  "customer_support_issue" longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  "company_id" int(11) NOT NULL,
  "archived" tinyint(1) NOT NULL,
  "archived_at" timestamp NULL DEFAULT NULL,
  "report_active" tinyint(4) DEFAULT NULL,
  "customer_screenshot" varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  "video_url" varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY ("id"),
  KEY "indexReportLookup" ("report_guid"),
  KEY "guid" ("report_guid"),
  KEY "customer_info_id_index" ("id")
)

Baseline:

 public function delete( Request $request )
    {
        // $id = (int)$request->id;
        // $customerRecord = CustomerInfo::find($id);
        // $foo_sql = $customerRecord->delete()->toSql();
        // echo($foo_sql);
        return 'test';
        //$customerRecord->delete();
    }

Ok so a brand new table, with a brand new Request. with a single ID in it, looks like this:

The controller looks like:

public function deleteTest( Request $request )
    {
        $id = (int)$request->id;
        $customerRecord = NewTable::where('id', '=', $id)->first();
        $customerRecord->delete();
        return response(null, 200);
    }

Postman version is :Version 7.27.1 (7.27.1)

1630 ms. WTF. 1.6 seconds for a simple request on a new table.

EXPLAIN DELETE:

1   DELETE  new_tables      range   PRIMARY PRIMARY 8   const   1   100 Using where

EXPLAIN SELECT

1   SIMPLE  new_tables      const   PRIMARY PRIMARY 8   const   1   100 Using index

---

MYSQL version 8.0.18 innodb_version 8.0.18

---

So now to add to the fun.

A framework free PHP file. Simple GET request. 100ms.

<?php
echo('tester');
?>

Edit. Just to reiterate.

A Laravel GET method (with authentication) returning test, returns 1.6s.

A no framework "sample.php" file returns in 100ms.

A Laravel GET method (without authentication) returning test, returns in 430ms.

A Laravel GET method (without authentication but with DB access), returns in 1483ms.

It look like there is indeed something holding up requests once the application starts using the database.

Route::middleware('auth:api')->get('/test1','Api\CustomerInfoController@deleteTest')->name('report.deleteTest1.api');
Route::middleware('auth:api')->get('/test2','Api\NewTableController@index')->name('report.deleteTest2.api');

Route::get('/test3','Api\CustomerInfoController@deleteTest')->name('report.deleteTest3.api');
Route::get('/test4','Api\NewTableController@index')->name('report.deleteTest4.api');
 Route::get('/test5','Api\NewTableController@dbTest')->name('report.deleteTest5.api');

NewTableController:

<?php

namespace App\Http\Controllers\Api;

class NewTableController extends Controller
{

    
    public function index()
    {
        return "test2";
    }



}

CustomerInfoController ( with some stuff removed, but method is pretty similar conceptually to NewTableController albeit with some dependency injection going on ).

<?php

namespace App\Http\Controllers\Api;
use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use App\Http\Requests\ReportDetailRequest;
use App\Services\CustomerInfoService;
use Auth;
use App\LookupParent;
use App\LookupChild;
use App\CustomerInfo;
use App\Http\Resources\CustomerInfoResourceCollection;
use App\Http\Resources\CustomerInfoResource;
use App\Http\Resources\CustomerInfoResourceDetail;
use Carbon\Carbon;
use App\NewTable;

class CustomerInfoController extends Controller
{
    protected $customerInfoService;

    public function __construct(
        CustomerInfoService $customerInfoService
        )
    {
        $this->customerInfoService = $customerInfoService;
    }


    public function deleteTest()
    {
        return 'deleteTest';
    }


   public function dbTest()
   {   
     tap(NewTable::find(1))->delete();
   }


}

Results:

/test1 (with authentication 1380ms)
/test2 (with authentication 1320ms)
/test3 (without authentication 112ms)
/test4 (without authentication 124ms)
/test5 (db without authentication 1483ms)

In other words, authentication talks to the database as does a simple delete query without authentication. These take at least a second to complete each. This leads to the roughly two second request mentioned above which has both elements (authentication and database access).

Edit. For those of you reading from Google. Problem was to do with the managed database provided by Digital Ocean. Setup a localised database on MySQL on the same box, and problem resolved itself. think it was either latency from datacenters between web server and database across the world somewhere or a misconfiguration on the part of the db admins at DigitalOcean. Resolved myself, problem wasn't Laravel.

Answer 1

try to delete the record without loading it:

public function delete( ReportDetailRequest $request )
{
   
    $customerRecord = CustomerInfo::where('id',$request->id)->delete();
    
}

please note that you don't have to cast $request->id to int

Answer 2

You don't need to fetch record from db to remove it

public function delete( ReportDetailRequest $request )
{
   
    return CustomerInfo::where('id',$request->input('id'))->delete();
    // it will return the count of deleted rows
}