Indexer Laravel Package

Getting Started

1. Install into a non-production environment first — this tool is for development/staging, not live traffic:

   composer require sarfraznawaz2005/indexer
   

2. Publish the config (and migrations, if the package ships a storage table):

   php artisan vendor:publish --provider="Sarfraznawaz2005\Indexer\IndexerServiceProvider"
   php artisan migrate
   

3. Register the middleware. The package hooks into the request lifecycle to observe SELECT queries. In older Laravel add it to the web group in app/Http/Kernel.php; in Laravel 11+ use bootstrap/app.php via ->withMiddleware(...). Check config/indexer.php for the enabled flag and any route/dashboard toggle.

4. Look first at: config/indexer.php (enable/disable, storage driver, route prefix) and the published migrations. That tells you whether results land in a DB table you can query directly or are exposed through a UI route / artisan command.

5. First use case: boot your app locally, click through your slowest screens (a listing page, a search form, a report), then open the dashboard/command the README documents and read the suggested index columns. You're looking for WHERE, ORDER BY, GROUP BY, and JOIN ... ON columns that lack an index.

Implementation Patterns

• Traffic-driven analysis, not synthetic. The value comes from real query shapes. Run it on staging with seeded data, exercise the app for a day or two, and review the accumulated suggestions in batches — one index migration covering the top offenders — rather than after every single request.
• Pair with timing data. This package tells you what to index, not how slow a query is. Combine it with Telescope, Debugbar, or the MySQL slow query log so you prioritize the queries that actually hurt. Don't add an index just because the tool flagged it.
• Review, then migrate manually. Treat every suggestion as a hint. Write an explicit migration:

  Schema::table('orders', function (Blueprint $table) {
      $table->index(['customer_id', 'created_at']); // composite, ordered by selectivity
  });
  

  Keep them in versioned migrations, never applied ad-hoc.
• Middleware group placement matters. Put it on the web group so it sees session-authenticated queries; putting it on api will miss everything behind your UI. If you only want one feature analyzed, add it as route middleware to that group instead.
• Schedule periodic cleanup. If results persist to a table, add a scheduled command (or DB::table(...)->truncate()) to reset between analysis rounds, otherwise stale suggestions from an old schema pollute the review.
• Before/after measurement. Snapshot the top queries, add indexes, then re-run the same flows and compare EXPLAIN output. The package's suggestion list should shrink; if a suggested column keeps reappearing, the index likely isn't being used (see gotchas).

Gotchas and Tips

• Never enable it in production. It adds middleware overhead, writes to your database on every request, and the storage table grows unbounded. Guard with APP_ENV in config and treat that as a hard rule.
• Package age. Last release was October 2021, targeting the Laravel 8/9 era. On Laravel 10/11 you may need to adjust the service provider's boot()/middleware registration or the route file. If it breaks, the fix is usually a one-liner in an app-level service provider — but budget for it before committing.
• Bindings are opaque. Query log entries store ? placeholders, so suggestions are structural: it can tell you orders.status is filtered, but not that 99% of rows are one status (a bad index candidate). Sanity-check selectivity with SELECT COUNT(DISTINCT col)/COUNT(*) before indexing.
• Only SELECT. INSERT/UPDATE/DELETE-driven indexes (foreign keys, uniqueness) won't be surfaced. Cover those from your schema review, not from this tool.
• Composite column order is everything. If two columns are always filtered together, the suggestion is a composite index — but the leading column must be the equality/high-selectivity one. Review order manually; the tool doesn't reason about cardinality or range-vs-equality.
• Stale after schema changes. If you add an index and the tool keeps recommending it, clear its stored records; it likely has no way to check information_schema for existing indexes.
• Joins and subqueries can produce suggestions for a table you don't control or for columns already covered by a covering index. Cross-check against SHOW INDEX FROM table before adding duplicates — a redundant left-prefix index wastes write throughput.
• Config caching bites. After publishing config changes run php artisan config:clear (or config:cache in CI); the middleware's enabled flag is read from config at boot.
• Extension points: the middleware is the natural place to override — subclass it (or bind your own class in the container) to filter out noise (e.g. skip LIKE-heavy search queries or queries against sessions/cache/jobs tables). The config file typically exposes column/keyword allow-lists for exactly this.
• Verify with EXPLAIN, always. A suggested index that MySQL won't use (low cardinality, leading wildcard LIKE '%x', functions on the column) is worse than none — it costs write performance and disk for nothing.