Slow Query from a table with 7 mil+ records

Hi All,

I have inherited a system that has a table for tracking page views. The table currently has around 7 million records in it.

I am using livewire and have written a query to display page views in a datatable.

The problem that I am having is that the query is taking like 20+ seconds, i assume this due to the number of records in the table.

$query = TrackingView::query()
            ->select([
                'tracking_views.id as id',
                'tracking_views.created_at as created_at',
                'users.id as user_id',
                'users.name as user_name',
                'users.email as user_email',
                'companies.id as company_id',
                'companies.name as company_name',
            ])
            ->leftJoin('users', 'tracking_views.user_id', '=', 'users.id')
            ->leftJoin('companies', 'users.company_id', '=', 'companies.id');

        if (isset($this->viewable)) {
            $query->whereHasMorph(
                'viewable',
                [get_class($this->viewable)],
                function (Builder $query) {
                    $query->where('id', $this->viewable->id);
                }
            );
        }

		//type could be 'guest', 'user' or 'all' 
        switch ($this->type) {
            case 'guest':
                $query->whereNull('tracking_views.user_id');
                break;
            case 'user':
                $query->whereHas('user', function ($query) {                   
                    if (! is_empty_string($this->userSearch)) {
                        $query->where(function (Builder $query) {
                            $query->where('name', 'like', '%'.$this->userSearch.'%')
                                ->orWhere('email', 'like', '%'.$this->userSearch.'%')
                                ->orWhereHas('company', function (Builder $query) {
                                    $query->where('name', 'like', '%'.$this->userSearch.'%');
                                });
                        });
                    }
                });
                break;
            default:
                $query->where(function (Builder $query) {
                    $query->whereNull('tracking_views.user_id')
                        ->orWhereHas('user', function ($query) {
                            if (! is_empty_string($this->userSearch)) {
                                $query->where(function (Builder $query) {
                                    $query->where('name', 'like', '%'.$this->userSearch.'%')
                                        ->orWhere('email', 'like', '%'.$this->userSearch.'%')
                                        ->orWhereHas('company', function (Builder $query) {
                                            $query->where('name', 'like', '%'.$this->userSearch.'%');
                                        });
                                });
                            }
                        });
                });
                break;
        }

        if (isset($this->startDate) && isset($this->endDate)) {
            $query->whereBetween('tracking_views.created_at', [$this->startDate, $this->endDate]);
        }

        return $query->orderBy($this->sortField, $this->sortDirection)
            ->paginate(10, ['*'], 'postViewsPage');

The table has a polymorphic relation to a "viewable" which I have tried adding an index to the viewable_type column but it didn't really have a massive effect on length of the query.

Schema::table('tracking_views', function (Blueprint $table) {
    $table->index('viewable_type', 'vt_index');
});

If anyone has any advice on what I can do to speed this up it would be much appreciated.away

Thanks

LaryAI

3 years ago

Level 58

There are a few things that can be done to optimize this query:

Add indexes to the columns used in the WHERE and JOIN clauses. In this case, it would be beneficial to add indexes to the following columns: tracking_views.user_id, users.id, users.company_id, companies.id, tracking_views.created_at, tracking_views.viewable_type, and tracking_views.viewable_id.
Use eager loading to reduce the number of queries being executed. In this case, it would be beneficial to eager load the user and company relationships.
Use pagination to limit the number of records being returned. In this case, the query is already using pagination, but it may be beneficial to reduce the number of records being returned per page.

Here's an updated version of the query that incorporates these optimizations:

$query = TrackingView::query()
    ->select([
        'tracking_views.id as id',
        'tracking_views.created_at as created_at',
        'users.id as user_id',
        'users.name as user_name',
        'users.email as user_email',
        'companies.id as company_id',
        'companies.name as company_name',
    ])
    ->leftJoin('users', 'tracking_views.user_id', '=', 'users.id')
    ->leftJoin('companies', 'users.company_id', '=', 'companies.id')
    ->with('user', 'user.company')
    ->where(function ($query) {
        $query->whereNull('tracking_views.user_id')
            ->orWhereHas('user', function ($query) {
                if (!is_empty_string($this->userSearch)) {
                    $query->where(function ($query) {
                        $query->where('name', 'like', '%' . $this->userSearch . '%')
                            ->orWhere('email', 'like', '%' . $this->userSearch . '%')
                            ->orWhereHas('company', function ($query) {
                                $query->where('name', 'like', '%' . $this->userSearch . '%');
                            });
                    });
                }
            });
    })
    ->when(isset($this->viewable), function ($query) {
        $query->where('viewable_type', get_class($this->viewable))
            ->where('viewable_id', $this->viewable->id);
    })
    ->when(isset($this->startDate) && isset($this->endDate), function ($query) {
        $query->whereBetween('tracking_views.created_at', [$this->startDate, $this->endDate]);
    })
    ->orderBy($this->sortField, $this->sortDirection)
    ->paginate(10, ['*'], 'postViewsPage');

Note that I've also simplified the switch statement by using the when method.

Please or to participate in this conversation.