Master database optimization in Laravel with advanced techniques including indexing, query optimization, and efficient relationship handling.
Efficient database management is crucial for application performance. This guide covers advanced techniques for optimizing database operations in Laravel applications.
// migrations/create_orders_table.php
public function up()
{
Schema::create('orders', function (Blueprint $table) {
$table->id();
$table->unsignedBigInteger('user_id');
$table->string('status');
$table->timestamp('created_at');
// Composite index for common queries
$table->index(['user_id', 'status', 'created_at']);
// Partial index for specific conditions
$table->index(['status', 'created_at'])
->where('status', 'pending');
});
}
// Optimize queries that only need indexed columns
Schema::table('products', function (Blueprint $table) {
// Index includes all columns needed by the query
$table->index(['sku', 'price', 'stock'], 'products_inventory_index');
});
// Usage example
Product::select(['sku', 'price', 'stock'])
->where('stock', '<', 10)
->orderBy('price')
->get();
// Complex aggregation query
$results = DB::select(DB::raw("
SELECT
DATE(created_at) as date,
COUNT(*) as total_orders,
SUM(amount) as revenue
FROM orders
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY DATE(created_at)
ORDER BY date DESC
"));
// With query bindings for safety
$results = DB::select("
SELECT * FROM orders
WHERE status = ?
AND created_at > ?
", ['completed', now()->subDays(30)]);
// Bad: Multiple queries
$users = User::all();
foreach ($users as $user) {
$latestOrder = $user->orders()->latest()->first();
}
// Good: Single query with joins
$users = User::select('users.*', 'orders.id as latest_order_id')
->leftJoin('orders', function ($join) {
$join->on('users.id', '=', 'orders.user_id')
->whereRaw('orders.id = (
SELECT id FROM orders o2
WHERE o2.user_id = users.id
ORDER BY created_at DESC
LIMIT 1
)');
})
->get();
class OrderController extends Controller
{
public function index(Request $request)
{
$query = Order::query();
// Conditional relationship loading
if ($request->includes_user) {
$query->with('user:id,name,email');
}
if ($request->includes_items) {
$query->with(['items' => function ($query) {
$query->select('id', 'order_id', 'product_id', 'quantity')
->with('product:id,name,price');
}]);
}
return $query->paginate(20);
}
}
class Order extends Model
{
public function loadRelationshipsIfNeeded()
{
if (!$this->relationLoaded('user')) {
$this->load('user:id,name,email');
}
if ($this->status === 'completed' && !$this->relationLoaded('payment')) {
$this->load('payment');
}
}
}
// Process large datasets efficiently
User::where('active', true)
->chunk(1000, function ($users) {
foreach ($users as $user) {
ProcessUserData::dispatch($user);
}
});
// Using lazy collection for memory efficiency
User::lazy()->each(function ($user) {
ProcessUserData::dispatch($user);
});
class ImportService
{
public function importProducts(array $products)
{
// Split array into chunks for better memory management
collect($products)->chunk(1000)->each(function ($chunk) {
DB::table('products')->insert($chunk->toArray());
});
}
public function updatePrices(array $priceUpdates)
{
collect($priceUpdates)->chunk(1000)->each(function ($chunk) {
$cases = [];
$ids = [];
foreach ($chunk as $update) {
$cases[] = "WHEN {$update['id']} THEN {$update['price']}";
$ids[] = $update['id'];
}
if (!empty($cases)) {
DB::update("
UPDATE products
SET price = CASE id
" . implode(' ', $cases) . "
END
WHERE id IN (" . implode(',', $ids) . ")
");
}
});
}
}
class OrderService
{
public function processOrder(Order $order)
{
DB::transaction(function () use ($order) {
// Update inventory
$this->updateInventory($order);
// Process payment
$this->processPayment($order);
// Update order status
$order->update(['status' => 'completed']);
}, 5); // 5 retry attempts
}
protected function updateInventory(Order $order)
{
foreach ($order->items as $item) {
// Use lockForUpdate to prevent race conditions
$product = Product::lockForUpdate()->find($item->product_id);
if ($product->stock < $item->quantity) {
throw new InsufficientStockException();
}
$product->decrement('stock', $item->quantity);
}
}
}
// config/database.php
'mysql' => [
// ...
'options' => extension_loaded('pdo_mysql') ? array_filter([
PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
PDO::ATTR_EMULATE_PREPARES => true,
PDO::ATTR_STRINGIFY_FETCHES => true,
]) : [],
],
// AppServiceProvider
public function boot()
{
if (config('app.debug')) {
DB::listen(function ($query) {
Log::channel('queries')->info(
$query->sql,
[
'bindings' => $query->bindings,
'time' => $query->time
]
);
});
}
}
class QueryAnalyzer
{
public static function analyze()
{
$results = DB::select('SHOW STATUS WHERE Variable_name LIKE "Slow_queries"');
$tableStats = DB::select('
SELECT
table_name,
table_rows,
data_length,
index_length
FROM information_schema.tables
WHERE table_schema = ?
', [config('database.connections.mysql.database')]);
return [
'slow_queries' => $results[0]->Value,
'table_stats' => $tableStats
];
}
}
// config/database.php
'mysql' => [
'read' => [
'host' => [
env('DB_READ_HOST1'),
env('DB_READ_HOST2'),
],
],
'write' => [
'host' => env('DB_WRITE_HOST'),
],
'sticky' => true,
// ...
],
// Usage in code
class UserRepository
{
public function getActiveUsers()
{
// Automatically uses read connection
return User::where('active', true)->get();
}
public function createUser(array $data)
{
// Automatically uses write connection
return User::create($data);
}
}
Regular Maintenance
Query Optimization
Data Consistency
Proper database optimization is crucial for application performance. Regular monitoring, proper indexing, and efficient query strategies can significantly improve your application's response time and scalability.