Courses

Structuring Databases in Laravel 11

Indexes: Useful, Useless and Composite

Summary of this lesson:
- Understanding index benefits
- Implementing column indexes
- Managing composite indexes
- Measuring index performance

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...

The full lesson is only for Premium Members.
Want to access all 18 lessons of this course? (81 min read)

You also get:

  • 69 courses (majority in latest Laravel 11)
  • Premium tutorials
  • Access to repositories
  • Private Discord