hoangnguyen0403/laravel-database-expert

Laravel Database Expert

Priority: P1 (HIGH)

Workflow: Optimize Slow Query

1. Profile query — Use DB::enableQueryLog() or Laravel Debugbar.
2. Add missing indexes — Create migration for join/where columns.
3. Replace N+1 — Use withCount(), withSum(), or addSelect subqueries.
4. Cache results — Apply Cache::remember() with tags for frequently accessed data.
5. Split reads/writes — Configure read/write keys in config/database.php.

Cache-Aside with Tags Example

See implementation examples for cache-aside pattern with tag-based invalidation.

Implementation Guidelines

Advanced Query Builder

• Complex Joins: Prefer joinSub($subquery, 'alias', ...) and whereExists(fn($q) => $q->select(DB::raw(1))...) over raw SQL or whereIn for correlated subqueries.
• Subqueries: Use addSelect with DB::raw subquery to avoid N+1 issues.
• Aggregates: Use withCount(), withSum(), and withAvg() directly via Eloquent for optimized column-based aggregation.
• Raw Expressions: Always use selectRaw or whereRaw with bindings; never use string concatenation in raw queries.

Caching Strategy (Redis/Memcached)

• Cache-Aside: Utilize Cache::remember('key', $ttl, $closure) for frequently accessed data (e.g., posts.all).
• Redis Tagging: Group related keys using Cache::tags(['posts', 'user:1']) for grouped invalidation.
• Invalidation: Call Cache::tags(['posts'])->flush() to clear specific subsets; never use Cache::flush() globally in production.

Scalability & Infrastructure

• Read/Write Splitting: Configure 'read' and 'write' keys in config/database.php mysql/pgsql connections. Laravel automatically routes SELECT to read and INSERT/UPDATE/DELETE to write; no code changes needed.
• Indices: Ensure correct database indexes present on all join and aggregate columns.

Anti-Patterns

• No string SQL concatenation: Use bindings or Query Builder.
• No queries in loops: Use subqueries, joins, or aggregates.
• No Cache::flush(): Use tags to target specific cache groups.
• No direct Redis calls: Use Laravel Cache wrappers consistently.

References

• Advanced SQL & Cache Patterns