laravel-query-optimizer

Optimize laravel DB or eloquent read queries using openAI API, and get insights on how you can make your queries faster

Installation

First, install the package using composer

composer require omaresmael/laravel-query-optimizer --dev

Then, publish openAI configuration file

php artisan vendor:publish --provider="OpenAI\Laravel\ServiceProvider"

Lastly, add the openAPI key to your .env file

OPENAI_API_KEY=sk-...

and that's it, you are ready to go

Usage

this package can help you optimize your eloquent or DB queries using openAI

User::query() //can be applied to `DB facade` as well (see tests)
    ->select('users.id', 'users.name')
    ->whereHas('roles', function ($query) {
        $query->where('name', 'author');
    })
    ->whereHas('posts', function ($query) {
        $query->where('title', 'Awesome post');
    })->optimize()->get();

the package has the following methods

optimize()

This method is responsible for optimizing the query and return an instance of the Optimizer class.

User::query() 
   ->select('users.id', 'users.name')
    ->whereHas('roles', function ($query) {
        $query->where('name', 'author');
    })
    ->whereHas('posts', function ($query) {
        $query->where('title', 'Awesome post');
    })->optimize();

//old query => select `id`, `name` from `users` where exists (select * from `roles` inner join `role_user` on `roles`.`id` = `role_user`.`role_id` where `users`.`id` = `role_user`.`user_id` and `type` = ?) and exists (select * from `posts` where `users`.`id` = `posts`.`user_id` and `title` = ?)

//optimized query => SELECT `id`, `name` FROM `users` INNER JOIN `role_user` ON `users`.`id` = `role_user`.`user_id` INNER JOIN `roles` ON `roles`.`id` = `role_user`.`role_id` INNER JOIN `posts` ON `users`.`id` = `posts`.`user_id` WHERE `type` = ? AND `title` = ?

toSql()

this method will return the optimized query as a string

User::query()
    ->select('users.id', 'users.name')
    ->whereHas('roles', function ($query) {
        $query->where('type', 'author');
    })
    ->whereHas('posts', function ($query) {
        $query->where('title', 'Awesome post');
    })->optimize()->toSql();
    
//output => SELECT `id`, `name` FROM `users` INNER JOIN `role_user` ON `users`.`id` = `role_user`.`user_id` INNER JOIN `roles` ON `roles`.`id` = `role_user`.`role_id` INNER JOIN `posts` ON `users`.`id` = `posts`.`user_id` WHERE `type` = ? AND `title` = ?

get()

this method will run the optimized query that is generated by optimize() method and return the result

⚠️ make sure to know the query you are running before using this method, as it will run the optimized query and not the original query

    User::query()
    ->select('users.id', 'users.name')
    ->whereHas('roles', function ($query) {
        $query->where('type', 'author');
    })
    ->whereHas('posts', function ($query) {
        $query->where('title', 'Awesome post');
    })->optimize()->get()->toArray();
    
    //output => ['id' => '1', 'name' => 'omar']

explain()

this method will return a key-value array that contains the optimized query, the reasoning behind performing such optimization, and suggestions to manually optimize the query even further

User::query()
    ->select('users.id', 'users.name')
    ->whereHas('roles', function ($query) {
        $query->where('type', 'author');
    })
    ->whereHas('posts', function ($query) {
        $query->where('title', 'Awesome post');
    })->optimize()->explain();

the array format will be

[
'optimizedQuery' => 'SELECT `id`, `name` FROM `users` INNER JOIN `role_user` ON `users`.`id` = `role_user`.`user_id` INNER JOIN `roles` ON `roles`.`id` = `role_user`.`role_id` INNER JOIN `posts` ON `users`.`id` = `posts`.`user_id` WHERE `type` = ? AND `title` = ?' // the optimized query
'reasoning'      => 'This query optimizes the original query by using JOINs to reduce the number of subqueries and improve the performance of the query. By using JOINs, the query can access the data from multiple tables in a single query, instead of having to make multiple subqueries.' //the reasoning behind performing such optimization,
'suggestions'    => 'It may be beneficial to add an index on the `type` and `title` columns to further improve the performance of the query.' //suggestions to manually optimize the query even further
]

Credits

Omar Esmaeel

License

The MIT License (MIT). Please see License File for more information.

Inspiration

This package is inspired by laravel-ask-database package
Thanks for the great work Marcel Pociot