Indexes: Useful, Useless and Composite

Now, let's talk about indexing the database columns, what impact it has, how to do that, and let's measure the performance.

Initial Project Structure

Imagine that you have some log of visitors to websites. You have a table of websites. I have seeded 100 rows.

Then, a table of visitors with IP address, country, user agent, page URL, and random dates was created at the field.

In the Controller, we take the country, for example, from the request and group visitors from that country by URL, counting the number of visitors per URL over some time period.

use App\Models\Visitor;
use Illuminate\Contracts\View\View;
 
class HomeController extends Controller
{
    public function __invoke(): View
    {
        $visitors = Visitor::selectRaw('websites.url, count(*) as visitors')
            ->where('country', request('country'))
            ->whereBetween('visitors.created_at', [
                now()->subDays(60)->startOfDay()->toDateTimeString(),
                now()->subDays(30)->endOfDay()->toDateTimeString(),
            ])
            ->join('websites', 'visitors.website_id', '=', 'websites.id')
            ->groupBy('website_id')
            ->orderByDesc('visitors')
            ->get();
 
        return view('home', compact('visitors'));
    }
}

Which gives results similar to the below:

In the debugbar, we can see that the query time is about 24ms, and the whole page is 128ms.

Now, what is the index, and how can we apply it here?

Applying Index

Indexes help query or filter one or more columns. In this case, we have a where...