Laravel Mysql Optimizer Laravel Package

Getting Started

Install and publish the config:

composer require gigerit/laravel-mysql-optimizer
php artisan vendor:publish --provider="MySQLOptimizer\ServiceProvider"

The provider is auto-discovered via extra.laravel.providers, so there is no manual registration step. There are no migrations, models, routes, or views to install — the entire package is one command, two actions, one job, and a config file.

Where to look first:

1. config/mysql-optimizer.php — the only thing you configure. At minimum it holds database (defaults to env('DB_DATABASE')) plus queue routing/runtime settings.
2. src/Actions/OptimizeTablesAction.php — ~one class of real logic. Read it before trusting it in production; it is short enough to audit in five minutes, which is the main risk mitigation for a package this small.
3. README.md scheduling + ops sections, then php artisan db:optimize --help to confirm the exact option surface in your installed version.

First use case — one table, low traffic, ad-hoc:

php artisan db:optimize --table=sessions

Then the realistic first production use case: a nightly scheduled job after your archival/pruning routine has deleted a large fraction of rows. That is where OPTIMIZE TABLE actually buys you something (reclaims space, rebuilds indexes, refreshes statistics) — running it on a churning OLTP table nightly usually just burns I/O.

Before scheduling anything, confirm the app's DB user has the privileges OPTIMIZE TABLE requires, and that you are pointed at the primary, not a read replica.

Implementation Patterns

Pattern 1 — scheduler entry (the default, correct shape). Prefer scheduling the queued job over the synchronous command so a slow table doesn't stall the scheduler process:

// routes/console.php or Kernel::schedule()
$schedule->command('db:optimize --queued --no-log')
    ->weeklyOn(0, '03:30')
    ->withoutOverlapping();

withoutOverlapping() matters because the job's default uniqueFor = 0 releases the uniqueness lock as soon as the job finishes, so a stalled or retried run can otherwise stack a second run behind it.

Pattern 2 — dispatch from application code after a known bulk event. The job is ShouldQueue + ShouldBeUnique, so it is safe to dispatch opportunistically after a big import, a soft-delete purge, or a tenant offboarding:

use MySQLOptimizer\Jobs\OptimizeTablesJob;

OptimizeTablesJob::dispatch()
    ->onQueue('maintenance')
    ->delay(now()->addMinutes(10));

Use onQueue + a dedicated low-priority queue worker so a 40-minute table rebuild never blocks customer-facing jobs.

Pattern 3 — per-table fan-out for large schemas. Set mysql-optimizer.queue.per_table to true only when you have hundreds of tables and a sequential monolithic run no longer fits inside your maintenance window. This fans out one unique OptimizeTableJob per table. It requires an explicit dedicated connection and queue that is serviced by exactly one worker — the package validates reservation settings before dispatch and again before DDL, but it cannot save you from a misconfigured worker pool.

Pattern 4 — multi-connection / multi-tenant apps. Use --database= to target a specific connection and keep the config's database key as the sensible default. For N tenants, generate N scheduled entries (one per connection) rather than trying to loop inside a single run — table name resolution goes through INFORMATION_SCHEMA, so each invocation is scoped to one schema.

Pattern 5 — treat results as data, not as console output. Results carry table, success, and timestamp. The package emits no events, so if you want Slack/email/PagerDuty notification on failure, wrap the call yourself:

$action = app(\MySQLOptimizer\Actions\OptimizeTablesAction::class);
$results = $action->execute(/* resolved database + tables */);
collect($results)->reject->success->each(fn ($r) => Log::warning('optimize failed', $r));

Calling the action directly from your own command is the cleanest extension point — it keeps the package's SQL/validation and gives you your own output, exit codes, and alerting.

Pattern 6 — verify in tests, don't execute. In app test suites, Queue::fake() and assert OptimizeTablesJob was pushed. Reserve real OPTIMIZE TABLE execution for a disposable integration database with temp tables, and never point the package at a shared or production DB from a test.

Gotchas and Tips

OPTIMIZE TABLE is not free, and this package has no --dry-run. On InnoDB it is effectively a table rebuild: it can lock the table, needs free disk roughly equal to the table's size for the temporary copy, and will fail messily if you run out of space mid-rebuild. Always rehearse against a restored copy of production for your largest table before scheduling it. Verify the current version's flag list with --help rather than trusting the docs' guideline list.

The queue reservation trap is the sharpest edge here. $tries, $backoff, and ShouldBeUnique are not protection against a Redis reservation expiring mid-DDL — and neither is uniqueFor. If retry_after is shorter than the job's timeout, the reserve expires while the table is still rebuilding, another worker picks the job up, and you get two concurrent OPTIMIZE TABLE statements on the same table. Required relationship:

job timeout (default 3600) + safety margin  <  retry_after  <  Horizon supervisor timeout (if Horizon)

Note the direction — the Horizon supervisor timeout must sit between the job timeout and retry_after, not above retry_after. If your biggest table can exceed 3600s, raise the timeout in the published config and widen retry_after and the supervisor timeout together. Never widen only one.

Per-table fan-out on a shared queue is a footgun. N workers pulling OptimizeTableJobs will rebuild several tables concurrently — table lock contention, connection-pool exhaustion, and I/O saturation. The package's own guidance is a dedicated connection + dedicated queue + exactly one worker. If you can't guarantee that topology, leave queue.per_table at false.

Exit codes and --no-log are your only observability. --no-log (and the job's disable-logging option) silences Laravel logging, which is fine when you're capturing results yourself but means a silent failure is genuinely silent. For scheduled runs, either keep logging on or wrap the action and log failures to your own channel.

Name resolution goes through INFORMATION_SCHEMA. That's good — it canonicalizes and quotes database/table names, so --table= can't be trivially abused for injection, and typos fail rather than silently no-op. But it also means a table created between the count and the run, or a view/information_schema entry you didn't expect, can surface as a failure in the result array rather than an exception. Check success per row instead of relying on the command's summary count.

MariaDB is not MySQL. OPTIMIZE TABLE semantics and supported engines differ (Aria, MyISAM, and InnoDB behave distinctly, and output columns differ). The package only surfaces success, so test on your actual engine and version rather than assuming parity. The repo's CI integration job runs MySQL 8.0 only.

Vendor risk is real but manageable. Three stars, zero dependents, and a repo name (laravel-mysql-optimize) that differs from the package name (laravel-mysql-optimizer) — easy to confuse when searching. Mitigations: pin an exact version in composer.json, note that the dependency surface is essentially zero (a big plus for a maintenance job), and keep the action class small enough in your head that forking it is a 30-minute job if maintenance stalls. Do not build anything on top of package internals beyond the documented command, job, action, and config.

Version floor to check before adopting. CI covers PHP 8.2–8.5 and Laravel 8–13. If you're still on PHP 8.1 or earlier, verify installability before planning around it — the docs advertise a wide Laravel range but the PHP matrix starts at 8.2.

Extension points are deliberately few: publish/override the config, dispatch OptimizeTablesJob yourself with onQueue/delay, invoke OptimizeTablesAction from your own command, and schedule through Laravel's scheduler. There are no events, no facades to swap, and no dry-run — anything richer than "optimize these tables and tell me which succeeded" belongs in your application layer, not in a fork of this package.