README

A Laravel package that intelligently optimizes database count queries by removing unnecessary JOINs and SELECT columns while preserving query logic and accuracy.

🚀 Features

Smart JOIN Removal: Automatically removes JOINs that aren't needed for count operations
Column Optimization: Replaces all SELECT columns with optimized COUNT(*)
Subquery Support: Handles complex queries with EXISTS, NOT EXISTS, and subqueries
Laravel Integration: Seamless integration with Laravel's Query Builder
Debug Mode: Detailed optimization information for performance analysis
Zero Configuration: Works out of the box with any Laravel project

📦 Installation

Install the package via Composer:

composer require mikailfaruqali/query-optimizer

The service provider will be automatically registered thanks to Laravel's package auto-discovery.

🔧 Usage

Basic Usage

use Illuminate\Support\Facades\DB;

// Original query with multiple JOINs
$query = DB::table('users')
    ->leftJoin('profiles', 'users.id', '=', 'profiles.user_id')
    ->leftJoin('posts', 'users.id', '=', 'posts.user_id')
    ->leftJoin('comments', 'posts.id', '=', 'comments.post_id')
    ->select('users.*', 'profiles.bio', 'posts.title')
    ->where('users.active', 1)
    ->where('profiles.verified', 1);

// Get optimized count (will only keep necessary JOINs)
$count = $query->optimizedCount();

Using Query Builder Macro

The package automatically registers convenient macros:

// Direct count
$count = $query->optimizedCount();

// With debug information
$debugInfo = $query->optimizedCountDebug();

Complex Queries with Subqueries

$query = DB::table('orders')
    ->leftJoin('customers', 'orders.customer_id', '=', 'customers.id')
    ->leftJoin('products', 'orders.product_id', '=', 'products.id')
    ->where('orders.status', 'completed')
    ->whereExists(function ($subquery) {
        $subquery->select(DB::raw(1))
            ->from('payments')
            ->whereColumn('payments.order_id', 'orders.id')
            ->where('payments.status', 'paid');
    });

// Only keeps JOINs needed for WHERE conditions and subqueries
$count = $query->optimizedCount();

Debug Information

Get detailed information about the optimization process:

$debugInfo = $query->optimizedCountDebug();

print_r($debugInfo);
/*
Array (
    [main_table] => users
    [required_tables] => Array (
        [0] => users
        [1] => profiles
    )
    [original_joins] => Array (
        [0] => profiles
        [1] => posts
        [2] => comments
    )
    [kept_joins] => Array (
        [0] => profiles
    )
    [removed_joins] => Array (
        [0] => posts
        [1] => comments
    )
    [optimization_strategy] => simple_count_no_groupby
    [performance_gain] => Array (
        [removed_joins_count] => 2
        [optimization_level] => maximum
    )
)
*/

🎯 How It Works

The optimizer analyzes your query and:

Identifies Required Tables: Scans WHERE conditions, subqueries, and EXISTS clauses
Removes Unnecessary JOINs: Only keeps JOINs that are actually referenced
Optimizes SELECT: Replaces all columns with COUNT(*)
Preserves Logic: Maintains all WHERE conditions and subqueries

Example Optimization

Before (Original Query):

SELECT users.*, profiles.bio, posts.title, comments.content 
FROM users 
LEFT JOIN profiles ON users.id = profiles.user_id 
LEFT JOIN posts ON users.id = posts.user_id 
LEFT JOIN comments ON posts.id = comments.post_id 
WHERE users.active = 1 
AND profiles.verified = 1

After (Optimized for Count):

SELECT COUNT(*) as count 
FROM users 
LEFT JOIN profiles ON users.id = profiles.user_id 
WHERE users.active = 1 
AND profiles.verified = 1

The posts and comments JOINs are removed because they're not referenced in any WHERE conditions.

🔍 Supported Query Types

✅ Basic WHERE conditions
✅ IN/NOT IN conditions
✅ EXISTS/NOT EXISTS subqueries
✅ Subqueries in WHERE clauses
✅ Date/Time conditions
✅ Raw SQL conditions
✅ All JOIN types (INNER, LEFT, RIGHT)

⚡ Performance Benefits

Reduced JOIN Operations: Eliminates unnecessary table joins
Faster Query Execution: Simpler queries execute faster
Lower Memory Usage: Fewer tables mean less memory consumption
Better Cache Utilization: Smaller result sets improve cache efficiency

🛡️ Safety

The optimizer is designed to be safe and maintains query accuracy:

Never modifies the original query object
Preserves all WHERE conditions and logic
Handles complex subqueries correctly
No risk of incorrect count results

📋 Requirements

PHP 7.4 or higher
Laravel 8.0 or higher

📝 License

This package is open-sourced software licensed under the MIT license.

🤝 Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

🐛 Bug Reports

If you discover any bugs, please create an issue on GitHub.

mikailfaruqali / query-optimizer

Maintainers

Details