A
A
Alexander M2018-04-15 12:17:08
MySQL
Alexander M, 2018-04-15 12:17:08

Very long query execution time (4903 sec - 81 min), what's wrong?

Good afternoon, I noticed that in production, the request takes about 2 seconds.
The site is powered by laravel and the query is executed using built-in syntax.

DB::table('wheels')
            ->select(DB::raw('count(DISTINCT models.id) as models_count, wheels.pcd, wheels.lz'))
            ->leftJoin('vehicles','vehicles.id','=','wheels.vehicle_id')
            ->leftJoin('modifications','modifications.id','=','vehicles.modification_id')
            ->leftJoin('models','models.id','=','modifications.model_id')
            ->groupBy('wheels.pcd','wheels.lz')
            ->orderBy('models_count','DESC')
            ->get();

Launched on a local, everything turned out to be worse. 81 minutes.
Started debugging slow queries. mysql sees the query like this:
select count(DISTINCT models.id) as models_count, wheels.pcd, wheels.lz 
from `wheels` 
left join `vehicles` on `vehicles`.`id` = `wheels`.`vehicle_id` 
left join `modifications` on `modifications`.`id` = `vehicles`.`modification_id` 
left join `models` on `models`.`id` = `modifications`.`model_id` 
group by `wheels`.`pcd`, `wheels`.`lz` 
order by `models_count` desc

Sending data - takes 99.97% of the request time.
Tables have many rows.
wheels - 495679
vehicles - 98088
models - 2006
modifications -
14997
5ad316fb600f2879097464.jpeg
in production, the request is generated in a couple of seconds. didn't cache.
locally 81 minutes. maybe, of course, the server is more powerful and grinds there))
On MySQL hosting - 5.7.21, on LAN 5.6
Here is the result of the query
at the bottom of the page - Manual selection by disk bolt pattern razboltovka.ru/

Answer the question

In order to leave comments, you need to log in

2 answer(s)
F
forspamonly2, 2018-04-15
@alexander7779

there is no schema in the normal form, no query plan.
but the picture shows the absence of a key on vehicles.id
if this table does not really have a primary key, then because of this it definitely slows down.

B
Boris Korobkov, 2018-04-15
@BorisKorobkov

RTFM https://dev.mysql.com/doc/refman/5.7/en/explain.html

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question