PHPackages                             erencagliz/laravel-index-advisor - PHPackages - PHPackages  [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register)

1. [Directory](/)
2. /
3. [Database & ORM](/categories/database)
4. /
5. erencagliz/laravel-index-advisor

ActiveLibrary[Database & ORM](/categories/database)

erencagliz/laravel-index-advisor
================================

Runtime query observer and index recommendation package for Laravel.

v1.0.0(2mo ago)10MITPHPPHP ^8.2CI passing

Since Mar 10Pushed 2mo agoCompare

[ Source](https://github.com/erencagliz/laravel-index-advisor)[ Packagist](https://packagist.org/packages/erencagliz/laravel-index-advisor)[ RSS](/packages/erencagliz-laravel-index-advisor/feed)WikiDiscussions main Synced 1mo ago

READMEChangelogDependencies (6)Versions (2)Used By (0)

Laravel Index Advisor
---------------------

[](#laravel-index-advisor)

[![Latest Version](https://camo.githubusercontent.com/d215090fe95ede51279bd3fef5f0a241e07f1dd39704e9e3f4c072a6b9f584c1/68747470733a2f2f696d672e736869656c64732e696f2f7061636b61676973742f762f6572656e6361676c697a2f6c61726176656c2d696e6465782d61647669736f722e7376673f7374796c653d666c61742d737175617265)](https://packagist.org/packages/erencagliz/laravel-index-advisor)[![Build Status](https://github.com/erencagliz/laravel-index-advisor/actions/workflows/index-advisor.yml/badge.svg)](https://github.com/erencagliz/laravel-index-advisor/actions)[![License](https://camo.githubusercontent.com/831ae0af1d4748e08d96d53e4e6704593c53eab99c6d1eb48b5d06ae99618bc6/68747470733a2f2f696d672e736869656c64732e696f2f6769746875622f6c6963656e73652f6572656e6361676c697a2f6c61726176656c2d696e6465782d61647669736f722e7376673f7374796c653d666c61742d737175617265)](LICENSE)

Laravel Index Advisor is a Laravel package that listens to your production queries and, based on recurring query patterns, produces **deterministic, explainable index recommendations** for missing or suboptimal indexes.

It is designed to be **safe to run in production**, with low overhead and clear, actionable output.

---

### What problem does it solve?

[](#what-problem-does-it-solve)

You already have tools like Telescope and Pulse that tell you **which queries are slow**.
What they don’t tell you is: **“Which index should I create?”**

Laravel Index Advisor:

- Observes queries at runtime (via `QueryExecuted` events),
- Normalizes them and aggregates by **query shape** (fingerprint),
- Inspects existing indexes on the target tables,
- Uses heuristics + EXPLAIN hints to detect **missing or weak indexes**,
- Produces index suggestions with a **confidence score** and a clear **reason**.

---

### Features

[](#features)

- **Production-safe query observation**

    - Hooks into Laravel’s `QueryExecuted` events.
    - Supports sampling and minimum duration thresholds to keep overhead low.
- **Query normalization & fingerprinting**

    - Rewrites literals to placeholders (`?`) and normalizes whitespace.
    - Groups the same logical query shape under a single fingerprint across different bindings.
- **AST-based SQL shape analysis**

    - Uses `php-sql-parser` under the hood.
    - Extracts tables, where / join / group / order columns and limit.
    - Detects subqueries and flags them conservatively.
- **Schema inspection** (Doctrine DBAL)

    - Reads existing indexes for each table (columns, order, uniqueness).
    - Detects similar or overlapping indexes to avoid noisy duplicates.
- **Rule-based suggestion engine with EXPLAIN hints**

    - Classic index patterns:
        - Single-column equality filter (`where user_id = ?`)
        - Multi-column equality (`where tenant_id = ? and status = ?`)
        - Equality + sort (`... order by created_at desc`)
        - Multi-tenant + soft delete (`tenant_id + deleted_at`)
    - Confidence score (0–100) uses:
        - Execution frequency,
        - Latency (avg / p95),
        - Existing indexes and similar indexes,
        - EXPLAIN signals (full scan, rows examined, filesort, temporary),
        - Parse confidence (AST success / subquery presence).
- **Workflow support**

    - Ignore rules (by fingerprint, table or table+columns).
    - Mark suggestions as accepted or dismissed with optional reasons.
- **Developer-friendly tooling**

    - Artisan commands for reporting, suggesting, analyzing and cleaning.
    - Facade/API to consume suggestions programmatically.
    - CI workflow example and comprehensive README.

---

### Installation

[](#installation)

```
composer require erencagliz/laravel-index-advisor

php artisan vendor:publish --tag=index-advisor-config
php artisan vendor:publish --tag=index-advisor-migrations
php artisan migrate
```

#### Requirements

[](#requirements)

- **Laravel**: 11, 12
- **PHP**: 8.2+
- **Databases**:
    - MySQL 8+ / MariaDB 10.6+
    - PostgreSQL 14+
    - SQLite (mainly for local/test usage)

---

### Quick start

[](#quick-start)

1. Install and publish config + migrations (see above).
2. Ensure `INDEX_ADVISOR_ENABLED=true` (or `enabled => true` in `config/index-advisor.php`).
3. Let your application handle real traffic for a while.

Then, inspect the collected queries:

```
php artisan index-advisor:report
```

This shows the most frequent and slowest query shapes (CLI table + optional `--json`).

Get index suggestions:

```
php artisan index-advisor:suggest --min-score=60
```

Persist suggestions for later review:

```
php artisan index-advisor:suggest --persist
```

Generate a migration from a specific suggestion:

```
php artisan index-advisor:generate-migration --suggestion=1
```

This creates a migration file under `database/migrations` that adds the recommended index.

---

### Example CLI output

[](#example-cli-output)

```
php artisan index-advisor:suggest --min-score=60
```

```
+--------+------------------------------+-------+------------+---------------------------------------------+
| Table  | Columns                      | Type  | Confidence | Reason                                      |
+--------+------------------------------+-------+------------+---------------------------------------------+
| orders | tenant_id, status, created_at| index | 87         | Frequent equality filters followed by sort…|
+--------+------------------------------+-------+------------+---------------------------------------------+

Use --json for full machine-readable details or --persist to store suggestions.

```

> **Note:** Index suggestions are hints based on observed workload and heuristics.
> Always review them in the context of your schema and business logic before applying migrations, especially on write-heavy tables.

---

### Workload report

[](#workload-report)

To get a high-level view of which tables are most heavily queried over a recent time window:

```
php artisan index-advisor:workload --days=7
```

This prints a summary and a table of per-table executions and timings.
Use `--json` to integrate with dashboards or CI tooling.

---

### Configuration

[](#configuration)

Main settings in `config/index-advisor.php`:

- **`enabled`**
    Turn the entire package on or off.
- **`sample_rate`**
    Float between 0.0–1.0. Controls what fraction of queries are sampled.
    For production, `0.1`–`0.5` is usually sufficient.
- **`min_query_time_ms`**
    Queries faster than this threshold are ignored. Helps reduce noise from cheap queries.
- **`min_executions`**
    Minimum number of executions per fingerprint before it is considered for suggestions.
    Very rare queries are ignored.
- **`retention_days`**
    How long to keep historical aggregates. Used together with `index-advisor:flush`.
- **`store_raw_sql_sample`**
    Defaults to `false`.
    When `true`, stores a single raw SQL sample per fingerprint (be mindful of PII/compliance).
- **`ignore_connections` / `ignore_tables` / `ignore_paths`**
    Connections, tables, and request paths to exclude from observation.
- **`explain.enabled`**
    Enables EXPLAIN-based analysis of sampled queries.
    When enabled, some EXPLAIN signals are included in `supporting_stats` and confidence scoring.

---

### How it works (high-level)

[](#how-it-works-high-level)

1. **Query observation**
    The service provider wires `QueryWatcher` to Laravel’s `QueryExecuted` events.
2. **Filtering**
    `QueryWatcher` checks:

    - `enabled`,
    - `sample_rate`,
    - `min_query_time_ms`,
    - `ignore_connections`, `ignore_tables`, `ignore_paths`to decide whether to record a query.
3. **Normalization & fingerprinting**

    - Literals are rewritten to placeholders (`?`), whitespace is normalized.
    - A deterministic fingerprint is generated from normalized SQL + connection + primary table.
4. **Aggregation**

    - `index_advisor_queries` stores per-fingerprint statistics:
        - executions, total\_time\_ms, avg\_time\_ms, p95\_time\_ms, max\_time\_ms, first/last seen.
5. **Shape & schema analysis**

    - AST-based parser extracts tables and column usage.
    - Doctrine DBAL reads existing indexes for the relevant tables.
6. **Suggestion engine**

    - Rule-based heuristics detect missing or suboptimal indexes for common patterns.
    - Similar indexes reduce confidence to avoid noisy/duplicate suggestions.
    - EXPLAIN is used (when enabled) to see whether the current plan does full scans, filesort, temporary tables, etc.
7. **Migration generation**

    - For any selected suggestion, a Laravel migration stub is generated, including both:
        - `up` (add index),
        - `down` (drop the same index).

---

### Limitations

[](#limitations)

- This is **not** a full cost-based optimizer; it focuses on common `SELECT` patterns and heuristic rules.
- Queries with heavy subqueries, unions, or window functions are treated conservatively and may not produce suggestions.
- Officially focused on MySQL/MariaDB/PostgreSQL; behavior on other drivers may vary.
- Write-heavy workloads may require more careful review of suggestions (index write overhead is not fully modeled yet).
- Migrations are **never applied automatically** – they always go through your normal deployment and migration workflow.

---

### Noise management (ignore / accept / dismiss)

[](#noise-management-ignore--accept--dismiss)

To keep long-term usage low-noise and focused:

- **Mark suggestions** as accepted or dismissed:

    ```
    php artisan index-advisor:mark 5 accepted
    php artisan index-advisor:mark 7 dismissed --reason="Handled manually"
    ```
- **Ignore patterns** you never want to see again:

    - Ignore by fingerprint:

        ```
        php artisan index-advisor:ignore --fingerprint=3f8b1d7a2c91 --reason="Legacy query"
        ```
    - Ignore an entire table:

        ```
        php artisan index-advisor:ignore --table=audits --reason="Log table"
        ```
    - Ignore a specific column pattern on a table:

        ```
        php artisan index-advisor:ignore --table=orders --columns=tenant_id,deleted_at --reason="Known pattern"
        ```

Ignored patterns are stored in the `index_advisor_ignores` table and are taken into account by the suggestion engine.

---

### Programmatic usage (Facade / service)

[](#programmatic-usage-facade--service)

You can also access suggestions programmatically via the facade:

```
use IndexAdvisor;

$suggestions = IndexAdvisor::suggest([
    'table' => 'orders',
    'min_score' => 70,
]);
```

Each suggestion is returned as an array with:

- table,
- columns,
- index\_type,
- reason,
- confidence,
- fingerprint,
- supporting\_stats (including optional EXPLAIN info),
- existing\_similar\_indexes,
- warnings.

This makes it easy to integrate with your own dashboards or tooling.

---

### CI integration (example)

[](#ci-integration-example)

An example GitHub Actions workflow is provided in `.github/workflows/index-advisor.yml`:

- Runs `composer install` and `vendor/bin/pest` on each push/PR.
- Ensures the package stays stable as you evolve it.

In host applications, you can additionally:

- Seed a realistic dataset,
- Hit a few critical endpoints/commands,
- Run `php artisan index-advisor:suggest --json` and attach the JSON to PRs as an artifact or PR comment.

---

### Tests

[](#tests)

The package uses Pest + Orchestra Testbench.

```
composer install

php vendor/bin/pest
```

Key coverage areas:

- Query normalizer and fingerprint determinism
- AST-based SQL shape parser behavior
- Suggestion engine heuristics:
    - Single-column equality
    - Multi-column equality + sort
    - Confidence reduction when similar indexes exist
- Service provider + config integration

---

### Contributing

[](#contributing)

When opening an issue, please include:

- Environment details (PHP, Laravel, DB versions)
- A sample of the relevant query or queries
- Expected vs. actual behavior

When sending a PR:

- Run the existing test suite
- Add unit/feature tests for new behavior
- Update the README where appropriate

Any feedback or contributions are very welcome and will make the package more useful for the community.

###  Health Score

37

—

LowBetter than 83% of packages

Maintenance87

Actively maintained with recent releases

Popularity2

Limited adoption so far

Community6

Small or concentrated contributor base

Maturity46

Maturing project, gaining track record

 Bus Factor1

Top contributor holds 100% of commits — single point of failure

How is this calculated?**Maintenance (25%)** — Last commit recency, latest release date, and issue-to-star ratio. Uses a 2-year decay window.

**Popularity (30%)** — Total and monthly downloads, GitHub stars, and forks. Logarithmic scaling prevents top-heavy scores.

**Community (15%)** — Contributors, dependents, forks, watchers, and maintainers. Measures real ecosystem engagement.

**Maturity (30%)** — Project age, version count, PHP version support, and release stability.

###  Release Activity

Cadence

Unknown

Total

1

Last Release

63d ago

### Community

Maintainers

![](https://www.gravatar.com/avatar/fb60de7dfc0f903d6903eb35bb8453a265762f004e69c151cb9085a201e13c7f?d=identicon)[erencagliz](/maintainers/erencagliz)

---

Top Contributors

[![erencagliz](https://avatars.githubusercontent.com/u/87371584?v=4)](https://github.com/erencagliz "erencagliz (4 commits)")

---

Tags

laraveldatabaseperformancequeryindexadvisor

###  Code Quality

TestsPest

### Embed Badge

![Health badge](/badges/erencagliz-laravel-index-advisor/health.svg)

```
[![Health](https://phpackages.com/badges/erencagliz-laravel-index-advisor/health.svg)](https://phpackages.com/packages/erencagliz-laravel-index-advisor)
```

###  Alternatives

[bvanhoekelen/performance

PHP performance tool analyser your script on time, memory usage and db query. Support Laravel and Composer for web, web console and command line interfaces.

521774.3k4](/packages/bvanhoekelen-performance)[sarfraznawaz2005/indexer

Laravel package to monitor SELECT queries and offer best possible INDEX fields.

562.7k](/packages/sarfraznawaz2005-indexer)[tpetry/laravel-query-expressions

Database-independent Query Expressions as a replacement to DB::raw calls

357436.5k2](/packages/tpetry-laravel-query-expressions)[renoki-co/l1

Laravel integration for Cloudflare Workers services.

16113.1k](/packages/renoki-co-l1)

PHPackages © 2026

[Directory](/)[Categories](/categories)[Trending](/trending)[Changelog](/changelog)[Analyze](/analyze)