php - MySQL count takes too long with many join -
in project, make 3 queries , 1 takes time.
use doctrine 2 , php , make request use querybuilder.
i've got list of vehicles (900 000 records), list of clients (600 000 records), utilisateur (600 000 records) , _client_has_groupe_etablissement (600 000 records).
in first query count number total of vehicles. take 0.25s => perfect.
secound query allow find vehicle different filter multiple joins. take 0.5s => perfect.
here query :
select [many things] vehicule v0_ inner join categorie_vehicule c1_ on v0_.idcategorievehicule = c1_.idcategorievehicule inner join energie e2_ on v0_.idenergie = e2_.idenergie left join vehicule_lastrdv v6_ on (v6_.idvehicule = v0_.idvehicule) left join client c4_ on v0_.idutilisateur = c4_.idutilisateur left join utilisateur u5_ on c4_.idutilisateur = u5_.idutilisateur left join _client_has_groupe_etablissement t3_ on c4_.idutilisateur = t3_.idutilisateur left join groupe_etablissement g7_ on t3_.idgroupeetablissement = g7_.idgroupeetablissement v0_.actifvehicule = 1 order v0_.idvehicule desc limit 207 offset 0; the last query count of total return rows of previous request :
select count(v0_.idvehicule) sclr_0 vehicule v0_ inner join categorie_vehicule c1_ on v0_.idcategorievehicule = c1_.idcategorievehicule inner join energie e2_ on v0_.idenergie = e2_.idenergie left join vehicule_lastrdv v3_ on v3_.idvehicule = v0_.idvehicule left join client c4_ on v0_.idutilisateur = c4_.idutilisateur left join utilisateur u5_ on c4_.idutilisateur = u5_.idutilisateur left join _client_has_groupe_etablissement t6_ on c4_.idutilisateur = t6_.idutilisateur left join groupe_etablissement g7_ on t6_.idgroupeetablissement = g7_.idgroupeetablissement v0_.actifvehicule = 1; but query take 20s
here explanation of request, seems good
id select_type table partitions type? possible_keys key key_len ref rows 1 primary v0_ ref idx_d0599d4b9f7ac00b,idx_d0599d4b323eb152,idx_vehicule_actifvehicule idx_vehicule_actifvehicule 1 const 440436 100.00 using 1 primary e2_ eq_ref primary primary 4 r4-p19d.v0_.idenergie 1 100.00 using index 1 primary c1_ eq_ref primary primary 4 r4-p19d.v0_.idcategorievehicule 1 100.00 using index 1 primary <derived2> ref <auto_key0> <auto_key0> 5 r4-p19d.v0_.idvehicule 10 100.00 1 primary c4_ eq_ref primary primary 4 r4-p19d.v0_.idutilisateur 1 100.00 using index 1 primary u5_ eq_ref primary primary 4 r4-p19d.c4_.idutilisateur 1 100.00 using index 1 primary t3_ ref primary,idx_30b01ade5d419ccb primary 4 r4-p19d.c4_.idutilisateur 1 100.00 using index 1 primary g7_ eq_ref primary primary 4 r4-p19d.t3_.idgroupeetablissement 1 100.00 using index 2 derived rdv index primary,idx_86645d0c4ffc60ed,idx_rdv_idrdv_daterdv_idagenda idx_rdv_idrdv_daterdv_idagenda 17 981941 33.33 using where; using index; using temporary; using filesort 2 derived agenda eq_ref primary,idx_2b41cd41ea190502 primary 4 r4-p19d.rdv.idagenda 1 100.00 using 2 derived etablissement eq_ref primary primary 4 r4-p19d.agenda.idetablissement 1 100.00 2 derived _vehicule_has_controle ref idx_1eaa071a9f61066b idx_1eaa071a9f61066b 5 r4-p19d.rdv.idrdv 1 100.00 i know how reduce execution time in mysql directly or doctrine.
i have tried sql_calc_found_rows, takes same time.
countrows or count in doctrine
Comments
Post a Comment