Download the PHP package kombee-technologies/smart-index-advisor without Composer

Smart Index Advisor

Automated database index optimizer for Laravel.
Analyses your PHP codebase, runtime query logs, and production DB statistics to recommend, score, and generate migration files for missing indexes — and flag unused ones for removal.

Supports MySQL, PostgreSQL, SQL Server, and SQLite.

Table of Contents

1. Requirements
2. Installation
3. Configuration
4. Environment Recommendations
5. Quick Start
6. Production Import Mode (CSV)
7. All Artisan Commands
8. Pipeline Steps Explained
9. Scoring Formula
10. Dashboard
11. Logging Configuration
12. Recommendation Status Lifecycle
13. Scheduler Setup
14. Table Name Mapping
15. Performance & Reliability
16. Troubleshooting

Requirements

Installation

1. Require the package

Option A — Stable release (recommended)

Install a tagged release using the ^1.0 version constraint:

Or add it manually to your composer.json and run composer update:

Version constraints:

• ^1.0 — allows >=1.0.0 <2.0.0 (minor and patch updates, locks major version) — recommended
• ~1.0 — allows >=1.0.0 <1.1.0 (patch updates only)
• 1.0.* — exactly 1.0.x

Option B — Development / latest commit

If you want to track the latest commit on master (e.g. during development or testing), add the VCS repository and use the dev-master constraint:

Then run:

Note: dev-master always pulls the latest commit and offers no stability guarantees. Use ^1.0 in production.

Upgrading an existing project from dev-master to a tagged release

Running composer update alone will not change your constraint — it only resolves within whatever you have declared. To switch to a tagged release, run:

This updates both composer.json and composer.lock in one step.

Note on Tokens: If the repository is private, Composer will ask for a Personal Access Token from GitHub when prompted (e.g., ghp_...).

Troubleshooting: If changes are not reflecting after an update, delete the vendor folder and re-download:

2. Install the Package

Just like Telescope, you can run a single artisan command to publish the configuration, service provider, and migrations:

2a. Register the Service Provider (Optional — for Dashboard Access)

The install command copies app/Providers/IndexAdvisorServiceProvider.php so you can customize the viewIndexAdvisor gate.

1. Register it:

   For Laravel 11+ (in bootstrap/providers.php):

   For Laravel 10 (in config/app.php under the providers array):

2. Disable auto-discovery in your app's composer.json to avoid loading the package provider twice:

3. Run composer dump-autoload.

Edit the published provider and add authorized emails in the gate() method.

3. Run migrations

This creates 5 tables:

4. Enable runtime logging (optional but recommended)

Add to your .env:

This registers a DB::listen hook that logs every SQL query executed during HTTP requests.

Configuration

After publishing, edit config/index_advisor.php. All values can also be set via .env. Defaults are production-safe.

config:cache note: All env() calls are resolved at config load time. When php artisan config:cache is active, env() returns null, so every setting falls back to its default value (which are production-safe). Override values by setting the corresponding environment variables before running config:cache.

Environment Recommendations

Each setting is controlled independently via its own env variable — no profile preset is needed. Just set the values appropriate for each environment in the corresponding .env file.

Runtime query logging is off by default in every environment. Set INDEX_ADVISOR_ENABLED=true to turn on DB::listen capture.

Recommended .env values per environment

Example: Local development .env

When query logic lives outside app/ (e.g. src/, modules/, or a path-repo package), add those directories:

Leave unset to scan only app/ (Laravel default).

Example: Production .env

Quick Start

Run the full pipeline in one command:

What this does:

1. Scans all PHP files in app/ for query patterns
2. Ingests DB engine statistics (pg_stat_statements, slow log, etc.)
3. Runs EXPLAIN on the slowest queries
4. Scores all candidates (0–100)
5. Prints the recommendations table

Then, to generate migration files for high-scored recommendations:

Production Import Mode (CSV)

When you cannot run the full pipeline against your production database, export statistics from production and import them locally.

Step 1 — Export from production database

Export the following CSV files from your production database. Queries are provided for both PostgreSQL and MySQL.

Unused Indexes (unused_indexes.csv)

PostgreSQL:

MySQL:

Note: sys.schema_unused_indexes requires MySQL 8.0+ and the sys schema. On MySQL 5.7, use performance_schema.table_io_waits_summary_by_index_usage instead (see alternative below).

MySQL 5.7 alternative (using performance_schema):

Sequential / Full Table Scans (seq_scans.csv)

PostgreSQL:

MySQL:

Tip: MySQL does not track sequential scans the same way PostgreSQL does. The COUNT_READ from table_io_waits_summary_by_table counts total row reads (including index reads). For a more accurate picture, subtract SUM_NO_INDEX_USED from the events_statements_summary_by_digest table (see slow queries below).

Slow Queries (slow_queries.csv)

PostgreSQL:

MySQL:

Note: MySQL performance_schema timers are in picoseconds — divide by 1,000,000,000 to get milliseconds. If performance_schema is disabled, enable it in my.cnf:

Restart MySQL and let it collect data for a few minutes before exporting.

Step 2 — Import on your local machine

The command auto-detects the file type by inspecting column headers.

Step 3 — Run in pure CSV mode

Important: Without --skip-local-db, Step 2 of the pipeline will re-query your local pg_stat_user_indexes and overwrite the DROP candidates you imported from CSV.

All Artisan Commands

index-advisor:run — Full Pipeline

Pipeline steps:

index-advisor:analyze-code — Static PHP Scan

Scans .php files under configurable directories (default: app/). Set scan roots via .env:

Paths may be absolute or relative to the Laravel project root. The command prints the resolved paths before scanning.

Uses regex patterns to extract column names from Eloquent/Query Builder calls and raw SQL strings. Detected patterns:

Results stored in index_advisor_columns.

index-advisor:ingest-slow-log — DB Telemetry

Ingests from:

• PostgreSQL: pg_stat_statements, pg_stat_user_tables, pg_stat_user_indexes
• MySQL: slow query log file + performance_schema
• SQL Server: sys.dm_exec_query_stats DMV
• SQLite: syncs index_advisor_queries → index_advisor_query_stats

index-advisor:import-stats — Import Production CSV/JSON

Imports production database statistics exported as CSV or JSON. Auto-detects file type:

index-advisor:run-explain — EXPLAIN Runner

Reads top N slowest queries from index_advisor_queries, runs EXPLAIN, detects full table scans, stores results in index_advisor_explains.

index-advisor:report — Console + Email Report

Prints all recommendations sorted by score. Optionally emails an HTML report with color-coded severity.

index-advisor:generate-migrations — Create Migration Files

Generates Laravel migration files with safe, production-ready DDL:

• MySQL: ALTER TABLE ... ADD INDEX ... ALGORITHM=INPLACE, LOCK=NONE
• PostgreSQL: CREATE INDEX CONCURRENTLY IF NOT EXISTS ...
• SQL Server: CREATE INDEX ... WITH (ONLINE=ON)

index-advisor:drop-unused — DROP INDEX Migrations

Generates DROP INDEX migration files for recommendations with index_type = DROP. Supports both PostgreSQL and MySQL.

Generated migrations use $withinTransaction = false only for PostgreSQL (required for DROP INDEX CONCURRENTLY). MySQL migrations retain full transaction safety.

index-advisor:dismiss — Dismiss Recommendations

index-advisor:mark-applied — Mark as Applied

Run this after php artisan migrate to update recommendation statuses.

index-advisor:reconcile — Sync with Live Schema

Re-reads live database indexes and dismisses stale recommendations. Run this after manually dropping or adding an index without going through the full pipeline.

index-advisor:purge — Data Retention Cleanup

Note: By default, pending and generated recommendations are never purged. Only applied and dismissed rows older than the retention period are deleted.

index-advisor:diagnose — Debug Missing Recommendations

Explains why COMPOSITE or REDUNDANT_CHECK recommendations are not appearing. Checks:

• Table row counts
• table_name = unknown inference failures
• Which columns are already indexed (for REDUNDANT_CHECK)
• Which runtime SQL samples have multi-column WHERE clauses (for COMPOSITE)

index-advisor:status — Check Logging Status

Shows whether HTTP query logging is active, the current profile, DB driver, and row counts for all five tables.

Pipeline Steps Explained

Step 1 — Static Code Analysis (analyze-code)

Reads .php files from INDEX_ADVISOR_CODE_PATHS (default: app/) and extracts column names from Eloquent/Query Builder patterns. Results go into index_advisor_columns. Table names are inferred from model class names or the table_map config.

Step 2 — DB Telemetry Ingestion (ingest-slow-log)

Reads actual query performance data from the database engine:

• PostgreSQL: pg_stat_statements (query stats) → writes to both index_advisor_queries and index_advisor_query_stats. pg_stat_user_indexes (unused indexes) → writes DROP candidates directly to index_advisor_recommendations.
• MySQL: slow query log + performance_schema
• SQL Server: sys.dm_exec_query_stats DMV

Step 3 — EXPLAIN Analysis (run-explain)

Reads top N queries from index_advisor_queries and runs EXPLAIN FORMAT=JSON. Detects full table scans (Seq Scan / access_type: ALL / Table Scan). Results stored in index_advisor_explains.

Step 4 — Scoring (ScoringService)

Correlates data from all four input tables using SqlColumnMatcher (clause-aware SQL parsing — not naive LIKE '%col%').

Score breakdown (max 100):

Filters applied before scoring:

• Table must have ≥ min_table_rows rows (default 5000 in production)
• Column must have ≥ min_cardinality distinct values (default 25 in production)
• Score must be ≥ min_score (default 20 in production)

COMPOSITE detection: Finds queries where 2+ columns from the same table appear in the WHERE/JOIN clause. Also parses runtime SQL directly to find multi-column predicates.

Step 5 — Report (report)

Prints a table of all recommendations sorted by score. Shows scoring evidence when expanded in the dashboard.

Step 6 — Generate Migrations (generate-migrations)

Creates Laravel migration files for recommendations with score >= auto_migrate_score. Updates status to generated.

Dashboard

Open in browser: http://your-app.test/index-advisor

The dashboard is disabled by default for security. Enable it explicitly:

The dashboard is protected by default middleware (web, auth, can:viewIndexAdvisor). The built-in gate only allows access in local and development environments. Publish the service provider to customize authorization (see Installation step 2b).

The dashboard provides:

• Recommendations — scored list with evidence breakdown, correlated SQL, and EXPLAIN plans
• Migrations — select specific recommendations and generate migration files
• Query Log — runtime queries with execution count, avg/max duration, and full-scan indicator
• Overview — row counts for all 5 tables plus environment info
• Upload — import production CSV/JSON statistics files directly from the browser

Dashboard actions:

• Run Analysis button — triggers index-advisor:run --report-only --skip-explain
• Generate Migrations button — triggers index-advisor:generate-migrations
• Dismiss — dismisses a recommendation with a reason
• Mark Applied — marks a recommendation as applied

SQL Sample Redaction

By default, SQL samples are redacted in the dashboard and API responses to prevent exposing sensitive data (e.g. PII in WHERE clauses). To show full SQL:

In non-production environments (local, development), this defaults to true. In production, it defaults to false.

API Pagination

The /api/recommendations endpoint supports optional pagination:

When per_page is provided, the response includes pagination metadata (current_page, last_page, total). Without per_page, all recommendations are returned (backward-compatible).

Self-Referential Query Prevention

Dashboard routes include the PreventIndexAdvisorLogging middleware, which marks requests so the query listener skips logging. This prevents the dashboard's own queries from being captured and creating a feedback loop.

To change the dashboard URL path:

Logging Configuration

Smart Index Advisor logs warnings and errors to a configurable channel and level:

When INDEX_ADVISOR_LOG_CHANNEL is not set (or null), the package uses the application's default log channel.

What gets logged:

Recommendation Status Lifecycle

Four recommendation types:

Scheduler Setup

Add to app/Console/Kernel.php (Laravel 10) or routes/console.php (Laravel 11+):

The package also registers these automatically via callAfterResolving(Schedule::class, ...).

Table Name Mapping

Your project uses abbreviated model names (e.g. LytLoginUsr, LymLeadMstr). The code analyzer tries to infer the DB table name from the PHP file name. When inference fails, the row is stored with table_name = 'unknown'.

Add manual overrides in config/index_advisor.php:

After adding entries, re-run the code analyzer:

Troubleshooting

index_advisor_queries is empty after running the pipeline

DB::listen only fires during HTTP requests, not during artisan commands. The pipeline bridges this by reading from pg_stat_statements.

Fix:

Postman requests not populating index_advisor_queries

Most likely cause: Telescope is enabled and recording. By default the listener still logs queries when Telescope is recording. To skip logging during Telescope recording:

If queries are still not appearing, ensure runtime logging is enabled:

Note: Queries are buffered during the HTTP request and flushed after the response is sent (via terminable middleware). If the process crashes before terminate() runs, buffered queries may be lost.

Report shows only DROP, no INDEX or COMPOSITE

index_advisor_queries is empty — the scoring engine has no runtime data to correlate.

Dropped an index but recommendation still shows REDUNDANT_CHECK

The reconciler uses cached schema data per process. Run:

Or for a specific column:

purge command not clearing recommendations

By design — pending recommendations are never purged. To clear everything:

Table name shows as unknown

The code analyzer could not infer the table from the file name. Add it to table_map in config:

COMPOSITE recommendations not appearing

Run the diagnose command to see exactly why:

Common reasons:

1. index_advisor_queries is empty (no runtime data)
2. Your queries only filter on one column at a time
3. The table has fewer than min_table_rows rows
4. A composite index already exists

pg_stat_statements not available

Add to postgresql.conf:

Restart PostgreSQL, use the app for a few minutes to generate query history, then:

Resetting Database Statistics

If you want to clear your database's internal statistics (to start tracking query performance from scratch after an index change or server upgrade), run the following queries:

PostgreSQL:

MySQL:

Command Quick Reference

Performance & Reliability

Buffered Query Logging

Runtime query logging uses an in-memory buffer instead of synchronous DB writes. During each HTTP request:

1. RuntimeQueryListener::handle() aggregates queries by fingerprint in memory (merging execution counts and durations)
2. After the response is sent to the client, FlushQueryBufferMiddleware::terminate() flushes all buffered queries in a single batch write

This reduces DB writes from 2 per query (1 query insert + 1 explain insert) to 1 batch per request, significantly reducing latency overhead.

Idempotent Migrations

The create_index_advisor_tables migration is idempotent — each Schema::create call is guarded by a Schema::hasTable() check. If the migration fails partway through, re-running it will skip already-created tables instead of throwing an error.

Migration timestamp note: Package migrations use 2026 timestamps. If your application has existing migrations from the same year, you may need to rename the published migration file so it sorts correctly. After running vendor:publish, rename if needed (e.g., 2026_01_01_000000_create_index_advisor_tables.php).

Conditional Transaction Safety

Generated DROP INDEX migrations only set $withinTransaction = false for PostgreSQL (required for DROP INDEX CONCURRENTLY). MySQL and other drivers retain full transaction safety, ensuring that if a DROP fails, it rolls back cleanly.

Post-Scoring Deduplication

Composite recommendation deduplication runs after all scoring is complete, not inside the scoring hot path. This means duplicates from the current run are collapsed immediately, and the scoring loop itself is not slowed down by a full-table dedup scan.

Selective Stale Cleanup

Runtime composite scoring uses selective stale cleanup instead of a delete-all-then-reinsert pattern. Only recommendations that were not regenerated in the current scoring run are deleted, avoiding unnecessary write load on large tables.

Composite Index Query Limit

You can control how many queries the system attempts to parse for complex multi-column relationships via your .env or config file:

If you set the limit to 1000, it will analyze the Top 1000 most frequently executed queries and skip the rest.

Here is exactly how the code works behind the scenes when looking for composite indexes:

1. It takes all the raw SQL queries your application ran.
2. It sorts them from highest to lowest based on execution count (orderByDesc('execution_count')).
3. It takes the top 1,000 queries.
4. Any query that ranks #1001 or lower is completely ignored for the composite calculation.

Why does it skip the rest? Calculating composite indexes (where the AI has to parse the raw SQL string, find the WHERE clauses, split multiple AND conditions, and match them against the tables) is highly intensive for PHP.

If your database has 50,000 unique queries and the package tried to parse all 50,000 in memory at once, your server would likely crash or run out of RAM (Allowed memory size exhausted).

By taking the Top 1000 most frequently executed queries, the package guarantees that it is only spending its computing power finding composite indexes for the queries that run the most often, which are the ones that actually matter for your application's speed!

License

MIT — © Kombee Technologies