I have one query that work pretty well:
SELECT [MANY THINGS]
FROM vehicule v0_ FORCE INDEX (PRIMARY)
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
WHERE v0_.actifVehicule = 1 AND
(v0_.pseudo LIKE '%ae%'
OR c1_.abrevCategorieVehicule LIKE '%ae%'
OR v0_.immatriculation LIKE '%ae%'
OR v0_.marque LIKE '%ae%'
OR v0_.modele LIKE '%ae%'
OR v0_.dateProchainControle LIKE '%ae%' OR t3_.codeClient LIKE '%ae%'
OR c4_.nomSociete LIKE '%ae%'
OR u5_.prenomUtilisateur LIKE '%ae%'
OR u5_.nomUtilisateur LIKE '%ae%'
OR u5_.telUtilisateur LIKE '%ae%'
OR u5_.mobileUtilisateur LIKE '%ae%'
OR v6_.maxDateRDV LIKE '%ae%'
OR v6_.villeEtablissement LIKE '%ae%'
OR e2_.libEnergie LIKE '%ae%')
ORDER BY v0_.idVehicule DESC LIMIT 207 OFFSET 0
With 800 000 vehicules, 600 000 clients and utilisateurs I've got a result in 1 second.
But I want the number total of results and I don't know how to did it fast (I can have 800 000 results).
I tried with a COUNT instead of all the select list but it takes 20s. I use doctrine and try with Paginator (because of the limit in the clause) but same result (20s).
I also tried with SQL_CALC_FOUND_ROWS
but same thing
Have you got an idea to have the number of rows very fast ?
Thanks a lot
EDIT : The EXPLAIN for the first query :
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 PRIMARY v0_ ref IDX_D0599D4B9F7AC00B,IDX_D0599D4B323EB152,idx_vehicule_actifVehicule idx_vehicule_actifVehicule 1 const 440436 100.00 Using where
1 PRIMARY e2_ eq_ref PRIMARY PRIMARY 4 r4-p19d.v0_.idEnergie 1 100.00
1 PRIMARY c1_ eq_ref PRIMARY PRIMARY 4 r4-p19d.v0_.idCategorieVehicule 1 100.00
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
1 PRIMARY u5_ eq_ref PRIMARY,idx_utilisateur_idUtilisateur_actifUtilisateur_mailUtilisateur PRIMARY 4 r4-p19d.c4_.idUtilisateur 1 100.00
1 PRIMARY t3_ ref PRIMARY,IDX_30B01ADE5D419CCB IDX_30B01ADE5D419CCB 4 r4-p19d.c4_.idUtilisateur 1 100.00
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 981942 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 where
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
And for the COUNT :
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 PRIMARY v0_ ref IDX_D0599D4B9F7AC00B,IDX_D0599D4B323EB152,idx_vehicule_actifVehicule idx_vehicule_actifVehicule 1 const 440436 100.00 Using where
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,idx_utilisateur_idUtilisateur_actifUtilisateur_mailUtilisateur 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 where
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
vehicule_lastrdv
is a view?