Livewire database query takes 81 seconds but running in database directly takes a few ms

[sts-ryan-holton]

Hi, I'm using Laravel 11 running through Octane and the latest version of Livewire. I'm using full page component concepts to render pages. I have a dynamic search input that searches the render method for the data, the fields I'm searching on are indexed and am using MySQL 8.

When running the database query presented directly through the database, it's almost instant, or a few seconds at most, but through Livewire and eloquent, the query is taking 80+ seconds. wondering if I'm missing something:

My component file

<?php

namespace App\Livewire\Pages\Applications;

use Livewire\Attributes\Url;
use Illuminate\Support\Str;
use Livewire\WithPagination;
use Livewire\Component;
use App\Models\Application;

class ApplicationIndex extends Component
{
    use WithPagination;

    #[Url]
    public string $search = '';
    public string $sortField = 'created_at';
    public string $sortDirection = 'desc';
    public bool $showAdvancedFilters = false;
    public array $filters = [
        'product' => null
    ];

    public function sortBy(string $field)
    {
        $this->sortDirection = $this->sortField === $field
            ? $this->sortDirection = $this->sortDirection == 'asc' ? 'desc' : 'asc'
            : 'asc';

        $this->sortField = $field;
    }

    public function updatedSearch()
    {
        $this->resetPage();
    }

    public function updatedFilters()
    {
        $this->resetPage();
    }


    public function render()
    {
        $this->authorize('viewAny', Application::class);

        $applications = Application::withCount('entries')->with([
            'entry'
        ])->when($this->filters['product'], fn ($query, $product) => $query->where('product_type', $product))
            ->searchExact(['id', 'hash', 'email', 'mobile', 'birthday'], $this->search)
            ->orderBy($this->sortField, $this->sortDirection)
            ->simplePaginate(10);

        return view('livewire.pages.applications.application-index', [
            'applications' => $applications
        ]);
    }
}

My SQL:

select
  applications.*,
  (
    select
      count(*)
    from
      application_entries
    where
      applications.id = application_entries.application_id
  ) as entries_count
from
  applications
where
  (
    id = 'a'
    or hash = 'a'
    or email = 'a'
    or mobile = 'a'
    or birthday = 'a'
  )
order by
  created_at desc
limit
  11 offset 0

[NBA707]

Have you verified the SQL eloquent is generating?

`

\DB::enableQueryLog(); // Enable query log

// Your Eloquent query executed by using get()

dd(\DB::getQueryLog()); // Show results of log

`

[NBA707]

Another thing I have done, is to log what queries go out to the database in the AppServiceProvider boot function:

 DB::connection('connection name')->listen(function ($query) {
                    Log::debug(
                        $query->sql,
                        $query->bindings,
                        $query->time
                    );
                });   

This will show you in the log the actual SQL execuited with any bind parameters. That way I can verify the query on the database is exactly what I expect.