How to optimize MYSQL Query with slow Order By

user_13095687 0
Viewed 166

I am trying to optimize my MYSQL query. I have only 2k rows in the database however the query as is takes almost 1,5 - 2 seconds. Because of other queries performed in the Laravel application the average load page with this query takes too long for a good user experience.

When I remove the order by, the query only takes 0,03 second. 

select * 
from `companies` 
     left join `model_has_status` on `companies`.`id` = `model_has_status`.`company_id` 
     left join `statuses` on `model_has_status`.`status_id` = `statuses`.`id`
where `model_has_status`.`status_id` = (select `status_id` 
                                        from `model_has_status` 
                                        where `company_id` = `companies`.`id` 
                                        order by `created_at` desc 
                                       limit 1) 
       and `companies`.`deleted_at` is null 
order by companies.`active_name` asc 
limit 30 offset 0

Mysql explain has the following output: screenshot of the MYSQL explain

I have tried to add an index on companies.active_name. However this was to no avail. Who can give me a pointer what to optimize?

To be complete this is the Laravel query I execute from the application: 

$companies = QueryBuilder::for(Company::class)
            ->leftjoin('model_has_status', 'companies.id', 'model_has_status.company_id')
            ->leftjoin('statuses', 'model_has_status.status_id', 'statuses.id')
            ->allowedFilters([
                AllowedFilter::custom('status', new FilterCompanyStatus()),
            ])
            ->where('model_has_status.status_id', function ($query) {
                $query->select('status_id')
                    ->from('model_has_status')
                    ->whereColumn('company_id', 'companies.id')
                    ->orderByDesc('created_at')
                    ->limit(1);
            })
            ->defaultSort('active_name')
            ->paginate(30);
laravel mysql
1 Answers

Thanks to the comments, I have done some more investigation into the inner workings of the database. I have been able to optimize the query results from 1,8 seconds average to 0,05 seconds!

The query made calls to three different tables: 

-Companies
*id
*active_name

-Model_has_status
*company_id
*status_id
*created_at

-Status
*id
*name

In the original question I only added a index to companies.active_name. That gave no speed improvement at all. However when I added indexes for all mentioned fields the query improved dramatically.

Just to be complete I just the following Laravel migrations to add the indexes.

Schema::table('companies', function (Blueprint $table) {
            $table->index('active_name');
        });

        Schema::table('model_has_status', function (Blueprint $table) {
            $table->index('status_id');
            $table->index('company_id');
            $table->index('created_at');
        });

        Schema::table('statuses', function (Blueprint $table) {
            $table->index('id');
        });
user_130956870
Related