MySQL query too long to execute

countjoin;MySQL

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

Best Answer

Abandon all hope!

First, there is a contradiction... You say FORCE INDEX(PRIMARY), yet PRIMARY is not an option; instead the better (actifVehicule) is being used. Please provide SHOW CREATE TABLE; it is difficult to guess at what indexes you have.

Here's what is going on...

  1. It is starting with V0 and and filtering on actifVehicule=1.
  2. Since (I assume you are using InnoDB), that index is really (actifVehicule, idVehicule), it can now avoid the "filesort" for the ORDER BY and scan directly through that index. Note: This (and only this) allows it to stop after LIMIT 207 matching rows.
  3. Apparently, most rows match pseudo LIKE '%ae%', so it finds 207 matches before having to scan too far. This would explain why the SELECT is faster than the COUNT.

For the COUNT(*) (which you have not shown; I am guessing that it starts SELECT COUNT(*) FROM ...), the entire set needs to be scanned. That includes all 800K rows of v0. Well, actually less than that because some don't satisfy actifVehicule=1; let's take the EXPLAIN's number of 440K. Plus, for each of those 440K rows, it must reach into the many tables that are OR'd together and check for %ae%. That adds up to about 9*440K random probes.

I'd say MySQL is pretty fast to take only 20 seconds to look at 4M rows scattered all over. You probably had a buffer_pool big enough to cache it all. It might have taken hours if it had been I/O bound.

SQL_CALC_FOUND_ROWS has to do both things -- find 207 rows and do the entire COUNT.

How to speed it up?

Plan A: Abandon "just enter a string, I'll find it anywhere"... Change the UI to require the user to say which field to search. Then have the correct indexes so that you can go from the searched field back to the Vehicle.

Plan B: Lump all the strings into one field in a new table, have the vehicleId in that same table; then start by searching that table. Oh, maintain that table so that it includes only 'active' vehicles. Then it is a scan of 440K rows, not 4M. So, the COUNT might be 10 times as fast. (And the regular SELECT will be somewhat faster.)

Related Question