|
Some reporting tools work with table pagination impose scanning records per offset and pages.
When a subquery is used for this uery time goes from few millisecond to minutes.
extract important part of subquery replace that lead to 10K time performance improvement
|
FROM financial_dossier dossier
|
INNER JOIN (SELECT id_dossier
|
FROM financial_dossier
|
ORDER BY id_dossier DESC
|
LIMIT 1000 offset 629000) AS dossier_limit
|
change to :
FROM (SELECT *
|
FROM financial_dossier
|
ORDER BY id_dossier DESC
|
LIMIT 1000 offset 629000) AS dossier
|
SELECT id_dossier
|
FROM financial_dossier
|
ORDER BY id_dossier DESC
|
LIMIT 1000 offset 629000
|
Table is innodb, id_dossier is primary key
sort cost is 0 so materialization of such query produce 1000 row
The optimizer compute wrong row estimate of the materialization of the pagination queries
A simplified query with no extra filtering on the self join is correct plan
explain select count(*) FROM financial_dossier dossier INNER JOIN (SELECT id_dossier FROM financial_dossier ORDER BY id_dossier DESC LIMIT 1000 OFFSET 643000) as dossier_limit ON dossier.id_dossier = dossier_limit.id_dossier;
|
+------+-------------+-------------------+--------+---------------+---------+---------+--------------------------+---------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------------------+--------+---------------+---------+---------+--------------------------+---------+-------------+
|
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 644000 | |
|
| 1 | PRIMARY | dossier | eq_ref | PRIMARY | PRIMARY | 3 | dossier_limit.id_dossier | 1 | Using index |
|
| 2 | DERIVED | financial_dossier | index | NULL | PRIMARY | 3 | NULL | 1138844 | Using index |
|
+------+-------------+-------------------+--------+---------------+---------+---------+--------------------------+---------+-------------+
|
derived2 number of rows 644000 is somehow over evaluated as it's evaluated as it lost the information of the limit i think it should be replace per 1000 to make the optimzer evaluate derived table order with the real number of rows inside the derived , by keeping 644000 it will always flavor table first with lower cardinality index
On the real query :
an index id_local filter the self joined table by half(569426) the derived is pushed later on the plan making half rows of the table to be left joined ignored the 1000 derived filtered by the pagination subquery
so paying a first cost of 644000 pk retrival save (569426-1000)*42 ref or eq_ref operations
The cardinality of the self joined derived get wrong and probably computed with 644000 in mind and not 1000
| 1 |
PRIMARY |
<derived2> |
ref |
key0 |
key0 |
3 |
financial_cf.dossier.id_dossier |
10 |
|
The self join is on a primary key ref (10) instead of eq_ref count of 1 , this may also badly impact the order of tables picked by the plan
- # Query_time: 135.814891 Lock_time: 0.000971 Rows_sent: 1000 Rows_examined: 42567341
- Rows_affected: 0 Bytes_sent: 169476
show explain for 12546;
|
+------+--------------------+------------------------------+--------+----------------------------------+--------------------------+---------+---------------------------------------------------------+---------+-----------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------------+------------------------------+--------+----------------------------------+--------------------------+---------+---------------------------------------------------------+---------+-----------------------------+
|
| 1 | PRIMARY | dossier | ref | PRIMARY,id_local | id_local | 2 | const | 569426 | Using where |
|
| 1 | PRIMARY | dr | eq_ref | PRIMARY | PRIMARY | 3 | financial_cf.dossier.id_dossier | 1 | |
|
| 1 | PRIMARY | parrain_filleul | ref | PRIMARY,id_dossier | PRIMARY | 3 | financial_cf.dr.id_emprunteur | 1 | Using where; Using index |
|
| 1 | PRIMARY | suivi_rc | eq_ref | PRIMARY | PRIMARY | 3 | financial_cf.dossier.id_suivi_rc | 1 | Using where |
|
| 1 | PRIMARY | rc | eq_ref | PRIMARY | PRIMARY | 2 | financial_cf.suivi_rc.id_utilisateur | 1 | Using where |
|
| 1 | PRIMARY | da | eq_ref | PRIMARY | PRIMARY | 2 | func | 1 | Using where; Using index |
|
| 1 | PRIMARY | at | eq_ref | PRIMARY | PRIMARY | 2 | func | 1 | Using where; Using index |
|
| 1 | PRIMARY | ar | eq_ref | PRIMARY | PRIMARY | 2 | func | 1 | Using where; Using index |
|
| 1 | PRIMARY | atc | eq_ref | PRIMARY | PRIMARY | 2 | func | 1 | Using where; Using index |
|
| 1 | PRIMARY | st | eq_ref | PRIMARY | PRIMARY | 2 | func | 1 | Using where; Using index |
|
| 1 | PRIMARY | parrain_client | eq_ref | PRIMARY | PRIMARY | 3 | financial_cf.parrain_filleul.id_parrain | 1 | Using where |
|
| 1 | PRIMARY | parrain_pro | ref | id_filleuil,id_dossier | id_filleuil | 3 | financial_cf.dr.id_emprunteur | 1 | Using where; Using index |
|
| 1 | PRIMARY | contact | eq_ref | PRIMARY | PRIMARY | 3 | financial_cf.parrain_pro.id_parrain_pro | 1 | Using where |
|
| 1 | PRIMARY | pre_ar | eq_ref | PRIMARY | PRIMARY | 2 | func | 1 | Using where; Using index |
|
| 1 | PRIMARY | pre_da | eq_ref | PRIMARY | PRIMARY | 2 | func | 1 | Using where; Using index |
|
| 1 | PRIMARY | ac_commission | eq_ref | PRIMARY | PRIMARY | 2 | func | 1 | Using where; Using index |
|
| 1 | PRIMARY | ma_commission | eq_ref | PRIMARY | PRIMARY | 2 | func | 1 | Using where; Using index |
|
| 1 | PRIMARY | at_commission | eq_ref | PRIMARY | PRIMARY | 2 | func | 1 | Using where; Using index |
|
| 1 | PRIMARY | rc_commission | eq_ref | PRIMARY | PRIMARY | 2 | func | 1 | Using where; Using index |
|
| 1 | PRIMARY | cc_commission | eq_ref | PRIMARY | PRIMARY | 2 | func | 1 | Using where; Using index |
|
| 1 | PRIMARY | da_commission | eq_ref | PRIMARY | PRIMARY | 2 | func | 1 | Using where; Using index |
|
| 1 | PRIMARY | ar_commission | eq_ref | PRIMARY | PRIMARY | 2 | func | 1 | Using where; Using index |
|
| 1 | PRIMARY | dc_commission | eq_ref | PRIMARY | PRIMARY | 2 | func | 1 | Using where; Using index |
|
| 1 | PRIMARY | att_commission | eq_ref | PRIMARY | PRIMARY | 2 | func | 1 | Using where; Using index |
|
| 1 | PRIMARY | local_commission | eq_ref | PRIMARY | PRIMARY | 2 | func | 1 | Using where; Using index |
|
| 1 | PRIMARY | commission | eq_ref | PRIMARY | PRIMARY | 3 | financial_cf.dossier.id_dossier | 1 | |
|
| 1 | PRIMARY | rdv | eq_ref | PRIMARY | PRIMARY | 3 | financial_cf.dr.id_rendez_vous_commercial | 1 | Using where |
|
| 1 | PRIMARY | complement | eq_ref | PRIMARY | PRIMARY | 3 | financial_cf.dr.id_donnee_dossier_emprunteur | 1 | |
|
| 1 | PRIMARY | dc | eq_ref | PRIMARY | PRIMARY | 3 | financial_cf.dossier.id_dossier | 1 | |
|
| 1 | PRIMARY | suivi_azur | eq_ref | PRIMARY | PRIMARY | 3 | financial_cf.dossier.id_suivi_azur | 1 | Using where |
|
| 1 | PRIMARY | suivi_cc | eq_ref | PRIMARY | PRIMARY | 3 | financial_cf.dossier.id_suivi_cc | 1 | Using where |
|
| 1 | PRIMARY | refus | eq_ref | PRIMARY | PRIMARY | 3 | financial_cf.dossier.id_dossier | 1 | |
|
| 1 | PRIMARY | depot_notaire | eq_ref | PRIMARY,id_dossier_refinancement | PRIMARY | 3 | func | 1 | Using where |
|
| 1 | PRIMARY | notaire_personne | eq_ref | PRIMARY | PRIMARY | 3 | financial_cf.depot_notaire.id_notaire_personne | 1 | Using where |
|
| 1 | PRIMARY | encaissement | eq_ref | PRIMARY | PRIMARY | 4 | financial_cf.dc.id_date_encaissement | 1 | Using where |
|
| 1 | PRIMARY | edition_offre | eq_ref | PRIMARY | PRIMARY | 4 | financial_cf.dc.id_edition_offre_pret | 1 | Using where |
|
| 1 | PRIMARY | depot_banque | eq_ref | PRIMARY | PRIMARY | 3 | func | 1 | Using where |
|
| 1 | PRIMARY | offre | ref | id_depot_banque | id_depot_banque | 3 | financial_cf.depot_banque.id_depot_banque | 1 | Using where |
|
| 1 | PRIMARY | validation | eq_ref | PRIMARY | PRIMARY | 3 | financial_cf.offre.id_offre | 1 | Using where |
|
| 1 | PRIMARY | offre2 | eq_ref | PRIMARY | PRIMARY | 3 | func | 1 | Using where |
|
| 1 | PRIMARY | validation2 | eq_ref | PRIMARY | PRIMARY | 3 | financial_cf.offre2.id_offre | 1 | Using where |
|
| 1 | PRIMARY | <derived2> | ref | key0 | key0 | 3 | financial_cf.dossier.id_dossier | 10 | |
|
| 24 | DEPENDENT SUBQUERY | financial_depot_banque_offre | ref | id_depot_banque | id_depot_banque | 3 | financial_cf.depot_banque.id_depot_banque | 1 | Using index |
|
| 22 | DEPENDENT SUBQUERY | dp | ref | PRIMARY,id_dossier_refinancement | id_dossier_refinancement | 3 | financial_cf.dr.id_dossier_refinancement | 1 | Using where; Using filesort |
|
| 22 | DEPENDENT SUBQUERY | sd | eq_ref | PRIMARY,id_depot_banque,etat | PRIMARY | 4 | func | 1 | Using where |
|
| 23 | DEPENDENT SUBQUERY | financial_suivi_depot | ref | id_depot_banque | id_depot_banque | 3 | financial_cf.dp.id_depot_banque | 1 | Using index |
|
| 20 | DEPENDENT SUBQUERY | financial_depot_notaire | ref | id_dossier_refinancement | id_dossier_refinancement | 3 | financial_cf.dossier.id_dossier | 1 | Using index |
|
| 19 | DEPENDENT SUBQUERY | histo_inter_doss | eq_ref | PRIMARY,id_local | PRIMARY | 3 | financial_cf.dossier.id_dossier | 1 | Using where |
|
| 19 | DEPENDENT SUBQUERY | local_com | eq_ref | PRIMARY | PRIMARY | 2 | financial_cf.histo_inter_doss.id_local | 1 | Using index |
|
| 18 | DEPENDENT SUBQUERY | histo_inter_doss_att | eq_ref | PRIMARY,id_att | PRIMARY | 3 | financial_cf.dossier.id_dossier | 1 | Using where |
|
| 18 | DEPENDENT SUBQUERY | u_att_com | eq_ref | PRIMARY | PRIMARY | 2 | financial_cf.histo_inter_doss_att.id_att | 1 | Using index |
|
| 17 | DEPENDENT SUBQUERY | histo_inter_doss_dc | eq_ref | PRIMARY,id_dc | PRIMARY | 3 | financial_cf.dossier.id_dossier | 1 | Using where |
|
| 17 | DEPENDENT SUBQUERY | u_dc_com | eq_ref | PRIMARY | PRIMARY | 2 | financial_cf.histo_inter_doss_dc.id_dc | 1 | Using index |
|
| 16 | DEPENDENT SUBQUERY | histo_inter_doss_ar | eq_ref | PRIMARY,id_ar | PRIMARY | 3 | financial_cf.dossier.id_dossier | 1 | Using where |
|
| 16 | DEPENDENT SUBQUERY | u_ar_com | eq_ref | PRIMARY | PRIMARY | 2 | financial_cf.histo_inter_doss_ar.id_ar | 1 | Using index |
|
| 15 | DEPENDENT SUBQUERY | histo_inter_doss_da | eq_ref | PRIMARY,id_da | PRIMARY | 3 | financial_cf.dossier.id_dossier | 1 | Using where |
|
| 15 | DEPENDENT SUBQUERY | u_da_com | eq_ref | PRIMARY | PRIMARY | 2 | financial_cf.histo_inter_doss_da.id_da | 1 | Using index |
|
| 14 | DEPENDENT SUBQUERY | histo_inter_doss_cc | eq_ref | PRIMARY,id_cc | PRIMARY | 3 | financial_cf.dossier.id_dossier | 1 | Using where |
|
| 14 | DEPENDENT SUBQUERY | u_cc_com | eq_ref | PRIMARY | PRIMARY | 2 | financial_cf.histo_inter_doss_cc.id_cc | 1 | Using index |
|
| 13 | DEPENDENT SUBQUERY | histo_inter_doss_rc | eq_ref | PRIMARY,id_rc | PRIMARY | 3 | financial_cf.dossier.id_dossier | 1 | Using where |
|
| 13 | DEPENDENT SUBQUERY | u_rc_com | eq_ref | PRIMARY | PRIMARY | 2 | financial_cf.histo_inter_doss_rc.id_rc | 1 | Using index |
|
| 12 | DEPENDENT SUBQUERY | histo_inter_doss_at | eq_ref | PRIMARY,id_at | PRIMARY | 3 | financial_cf.dossier.id_dossier | 1 | Using where |
|
| 12 | DEPENDENT SUBQUERY | u_at_com | eq_ref | PRIMARY | PRIMARY | 2 | financial_cf.histo_inter_doss_at.id_at | 1 | Using index |
|
| 11 | DEPENDENT SUBQUERY | histo_inter_doss_ma | eq_ref | PRIMARY,id_ma | PRIMARY | 3 | financial_cf.dossier.id_dossier | 1 | Using where |
|
| 11 | DEPENDENT SUBQUERY | u_ma_com | eq_ref | PRIMARY | PRIMARY | 2 | financial_cf.histo_inter_doss_ma.id_ma | 1 | Using index |
|
| 10 | DEPENDENT SUBQUERY | histo_inter_doss_ac | eq_ref | PRIMARY,id_ac | PRIMARY | 3 | financial_cf.dossier.id_dossier | 1 | Using where |
|
| 10 | DEPENDENT SUBQUERY | u_ac_com | eq_ref | PRIMARY | PRIMARY | 2 | financial_cf.histo_inter_doss_ac.id_ac | 1 | Using index |
|
| 9 | DEPENDENT SUBQUERY | pre_s_da | ref | PRIMARY,id_employe | id_employe | 2 | financial_cf.contact.id_commercial | 1 | Using index |
|
| 9 | DEPENDENT SUBQUERY | pre_u_da | eq_ref | PRIMARY,type_utilisateur | PRIMARY | 2 | financial_cf.pre_s_da.id_superieur | 1 | Using where |
|
| 8 | DEPENDENT SUBQUERY | pre_s_ar | ref | PRIMARY,id_employe | id_employe | 2 | financial_cf.contact.id_commercial | 1 | Using index |
|
| 8 | DEPENDENT SUBQUERY | pre_u_ar | eq_ref | PRIMARY,type_utilisateur | PRIMARY | 2 | financial_cf.pre_s_ar.id_superieur | 1 | Using where |
|
| 7 | DEPENDENT SUBQUERY | r_st | ref | id_rule,idx_rule | idx_rule | 767 | const | 1 | Using index condition |
|
| 7 | DEPENDENT SUBQUERY | s_st | ref | PRIMARY,id_employe | id_employe | 2 | financial_cf.rc.id_utilisateur | 1 | Using index |
|
| 7 | DEPENDENT SUBQUERY | gu_st | ref | PRIMARY,id_utilisateur | id_utilisateur | 2 | financial_cf.s_st.id_superieur | 1 | Using index |
|
| 7 | DEPENDENT SUBQUERY | rg_st | eq_ref | PRIMARY,id_rule,id_group | PRIMARY | 4 | financial_cf.r_st.id_rule,financial_cf.gu_st.id_group | 1 | Using index |
|
| 7 | DEPENDENT SUBQUERY | u_st | eq_ref | PRIMARY,type_utilisateur | PRIMARY | 2 | financial_cf.s_st.id_superieur | 1 | Using where |
|
| 6 | DEPENDENT SUBQUERY | r_atc | ref | id_rule,idx_rule | idx_rule | 767 | const | 1 | Using index condition |
|
| 6 | DEPENDENT SUBQUERY | s_atc | ref | PRIMARY,id_employe | id_employe | 2 | financial_cf.rc.id_utilisateur | 1 | Using index |
|
| 6 | DEPENDENT SUBQUERY | gu_atc | ref | PRIMARY,id_utilisateur | id_utilisateur | 2 | financial_cf.s_atc.id_superieur | 1 | Using index |
|
| 6 | DEPENDENT SUBQUERY | rg_atc | eq_ref | PRIMARY,id_rule,id_group | PRIMARY | 4 | financial_cf.r_atc.id_rule,financial_cf.gu_atc.id_group | 1 | Using index |
|
| 6 | DEPENDENT SUBQUERY | u_atc | eq_ref | PRIMARY,type_utilisateur | PRIMARY | 2 | financial_cf.s_atc.id_superieur | 1 | Using where |
|
| 5 | DEPENDENT SUBQUERY | s_ar | ref | PRIMARY,id_employe | id_employe | 2 | financial_cf.rc.id_utilisateur | 1 | Using index |
|
| 5 | DEPENDENT SUBQUERY | u_ar | eq_ref | PRIMARY,type_utilisateur | PRIMARY | 2 | financial_cf.s_ar.id_superieur | 1 | Using where |
|
| 4 | DEPENDENT SUBQUERY | r_at | ref | id_rule,idx_rule | idx_rule | 767 | const | 1 | Using index condition |
|
| 4 | DEPENDENT SUBQUERY | s_at | ref | PRIMARY,id_employe | id_employe | 2 | financial_cf.rc.id_utilisateur | 1 | Using index |
|
| 4 | DEPENDENT SUBQUERY | gu_at | ref | PRIMARY,id_utilisateur | id_utilisateur | 2 | financial_cf.s_at.id_superieur | 1 | Using index |
|
| 4 | DEPENDENT SUBQUERY | rg_at | eq_ref | PRIMARY,id_rule,id_group | PRIMARY | 4 | financial_cf.r_at.id_rule,financial_cf.gu_at.id_group | 1 | Using index |
|
| 4 | DEPENDENT SUBQUERY | u_at | eq_ref | PRIMARY,type_utilisateur | PRIMARY | 2 | financial_cf.s_at.id_superieur | 1 | Using where |
|
| 3 | DEPENDENT SUBQUERY | s_da | ref | PRIMARY,id_employe | id_employe | 2 | financial_cf.rc.id_utilisateur | 1 | Using index |
|
| 3 | DEPENDENT SUBQUERY | u_da | eq_ref | PRIMARY,type_utilisateur | PRIMARY | 2 | financial_cf.s_da.id_superieur | 1 | Using where |
|
| 2 | DERIVED | financial_dossier | index | NULL | PRIMARY | 3 | NULL | 1138852 | Using index |
|
+------+--------------------+------------------------------+--------+----------------------------------+--------------------------+---------+---------------------------------------------------------+---------+-----------------------------+
|
{
|
\n "query_block":{
|
\n "select_id":1,
|
\n "r_loops":1,
|
\n "r_total_time_ms":26410,
|
\n "table":{
|
\n "table_name":"dossier",
|
\n "access_type":"ref",
|
\n "possible_keys":[
|
"PRIMARY",
|
"id_local"
|
],
|
\n "key":"id_local",
|
\n "key_length":"2",
|
\n "used_key_parts":[
|
"id_local"
|
],
|
\n "ref":[
|
"const"
|
],
|
\n "r_loops":1,
|
\n "rows":570196,
|
\n "r_rows":1.14e6,
|
\n "r_total_time_ms":2366.2,
|
\n "filtered":100,
|
\n "r_filtered":100,
|
\n "attached_condition":"dossier.id_local <=> 1" \n
|
},
|
\n "table":{
|
\n "table_name":"dr",
|
\n "access_type":"eq_ref",
|
\n "possible_keys":[
|
"PRIMARY"
|
],
|
\n "key":"PRIMARY",
|
\n "key_length":"3",
|
\n "used_key_parts":[
|
"id_dossier_refinancement"
|
],
|
\n "ref":[
|
"financial_cf.dossier.id_dossier"
|
],
|
\n "r_loops":1143114,
|
\n "rows":1,
|
\n "r_rows":1,
|
\n "r_total_time_ms":2624.5,
|
\n "filtered":100,
|
\n "r_filtered":100 \n
|
},
|
\n "table":{
|
\n "table_name":"parrain_filleul",
|
\n "access_type":"ref",
|
\n "possible_keys":[
|
"PRIMARY",
|
"id_dossier"
|
],
|
\n "key":"PRIMARY",
|
\n "key_length":"3",
|
\n "used_key_parts":[
|
"id_filleul"
|
],
|
\n "ref":[
|
"financial_cf.dr.id_emprunteur"
|
],
|
\n "r_loops":1143114,
|
\n "rows":1,
|
\n "r_rows":0.0311,
|
\n "r_total_time_ms":1562.3,
|
\n "filtered":100,
|
\n "r_filtered":68.107,
|
\n "attached_condition":"trigcond(parrain_filleul.id_dossier = dossier.id_dossier)",
|
\n "using_index":true \n
|
},
|
\n "table":{
|
\n "table_name":"parrain_pro",
|
\n "access_type":"ref",
|
\n "possible_keys":[
|
"id_filleuil",
|
"id_dossier"
|
],
|
\n "key":"id_filleuil",
|
\n "key_length":"3",
|
\n "used_key_parts":[
|
"id_filleul"
|
],
|
\n "ref":[
|
"financial_cf.dr.id_emprunteur"
|
],
|
\n "r_loops":1143114,
|
\n "rows":1,
|
\n "r_rows":0.0489,
|
\n "r_total_time_ms":1640.1,
|
\n "filtered":100,
|
\n "r_filtered":93.893,
|
\n "attached_condition":"trigcond(parrain_pro.id_dossier = dossier.id_dossier)",
|
\n "using_index":true \n
|
},
|
\n "table":{
|
\n "table_name":"dc",
|
\n "access_type":"eq_ref",
|
\n "possible_keys":[
|
"PRIMARY"
|
],
|
\n "key":"PRIMARY",
|
\n "key_length":"3",
|
\n "used_key_parts":[
|
"id_dossier_refinancement"
|
],
|
\n "ref":[
|
"financial_cf.dossier.id_dossier"
|
],
|
\n "r_loops":1143114,
|
\n "rows":1,
|
\n "r_rows":0.2046,
|
\n "r_total_time_ms":2115.3,
|
\n "filtered":100,
|
\n "r_filtered":100 \n
|
},
|
\n "table":{
|
\n "table_name":"depot_banque",
|
\n "access_type":"eq_ref",
|
\n "possible_keys":[
|
"PRIMARY"
|
],
|
\n "key":"PRIMARY",
|
\n "key_length":"3",
|
\n "used_key_parts":[
|
"id_depot_banque"
|
],
|
\n "ref":[
|
"func"
|
],
|
\n "r_loops":1143114,
|
\n "rows":1,
|
\n "r_rows":0.0235,
|
\n "r_total_time_ms":50.865,
|
\n "filtered":100,
|
\n "r_filtered":100,
|
\n "attached_condition":"trigcond(depot_banque.id_depot_banque = (subquery#22))",
|
\n "using_index":true \n
|
},
|
\n "table":{
|
\n "table_name":"offre",
|
\n "access_type":"ref",
|
\n "possible_keys":[
|
"id_depot_banque"
|
],
|
\n "key":"id_depot_banque",
|
\n "key_length":"3",
|
\n "used_key_parts":[
|
"id_depot_banque"
|
],
|
\n "ref":[
|
"financial_cf.depot_banque.id_depot_banque"
|
],
|
\n "r_loops":1143114,
|
\n "rows":1,
|
\n "r_rows":0.0236,
|
\n "r_total_time_ms":82.029,
|
\n "filtered":100,
|
\n "r_filtered":100,
|
\n "attached_condition":"trigcond(trigcond(depot_banque.id_depot_banque is not null))",
|
\n "using_index":true \n
|
},
|
\n "table":{
|
\n "table_name":"offre2",
|
\n "access_type":"eq_ref",
|
\n "possible_keys":[
|
"PRIMARY"
|
],
|
\n "key":"PRIMARY",
|
\n "key_length":"3",
|
\n "used_key_parts":[
|
"id_offre"
|
],
|
\n "ref":[
|
"func"
|
],
|
\n "r_loops":1143331,
|
\n "rows":1,
|
\n "r_rows":0.0236,
|
\n "r_total_time_ms":85.302,
|
\n "filtered":100,
|
\n "r_filtered":100,
|
\n "attached_condition":"trigcond(offre2.id_offre = (subquery#24))",
|
\n "using_index":true \n
|
},
|
\n "table":{
|
\n "table_name":"suivi_rc",
|
\n "access_type":"eq_ref",
|
\n "possible_keys":[
|
"PRIMARY"
|
],
|
\n "key":"PRIMARY",
|
\n "key_length":"3",
|
\n "used_key_parts":[
|
"id_gestionsuivi"
|
],
|
\n "ref":[
|
"financial_cf.dossier.id_suivi_rc"
|
],
|
\n "r_loops":1143331,
|
\n "rows":1,
|
\n "r_rows":0.2793,
|
\n "r_total_time_ms":892.91,
|
\n "filtered":100,
|
\n "r_filtered":100,
|
\n "attached_condition":"trigcond(trigcond(dossier.id_suivi_rc is not null))" \n
|
},
|
\n "table":{
|
\n "table_name":"rc",
|
\n "access_type":"eq_ref",
|
\n "possible_keys":[
|
"PRIMARY"
|
],
|
\n "key":"PRIMARY",
|
\n "key_length":"2",
|
\n "used_key_parts":[
|
"id_utilisateur"
|
],
|
\n "ref":[
|
"financial_cf.suivi_rc.id_utilisateur"
|
],
|
\n "r_loops":1143331,
|
\n "rows":1,
|
\n "r_rows":0.2793,
|
\n "r_total_time_ms":556.71,
|
\n "filtered":100,
|
\n "r_filtered":100,
|
\n "attached_condition":"trigcond(trigcond(suivi_rc.id_utilisateur is not null))",
|
\n "using_index":true \n
|
},
|
\n "table":{
|
\n "table_name":"depot_notaire",
|
\n "access_type":"eq_ref",
|
\n "possible_keys":[
|
"PRIMARY",
|
"id_dossier_refinancement"
|
],
|
\n "key":"PRIMARY",
|
\n "key_length":"3",
|
\n "used_key_parts":[
|
"id_depot_notaire"
|
],
|
\n "ref":[
|
"func"
|
],
|
\n "r_loops":1143331,
|
\n "rows":1,
|
\n "r_rows":0.0127,
|
\n "r_total_time_ms":35.896,
|
\n "filtered":100,
|
\n "r_filtered":100,
|
\n "attached_condition":"trigcond(depot_notaire.id_dossier_refinancement = dossier.id_dossier and depot_notaire.id_depot_notaire = (subquery#20))" \n
|
},
|
\n "table":{
|
\n "table_name":"<derived2>",
|
\n "access_type":"ref",
|
\n "possible_keys":[
|
"key0"
|
],
|
\n "key":"key0",
|
\n "key_length":"3",
|
\n "used_key_parts":[
|
"id_dossier"
|
],
|
\n "ref":[
|
"financial_cf.dossier.id_dossier"
|
],
|
\n "r_loops":1143331,
|
\n "rows":10,
|
\n "r_rows":8.7e-4,
|
\n "r_total_time_ms":209.55,
|
\n "filtered":100,
|
\n "r_filtered":100,
|
\n "materialized":{
|
\n "query_block":{
|
\n "select_id":2,
|
\n "r_loops":1,
|
\n "r_total_time_ms":197.45,
|
\n "table":{
|
\n "table_name":"financial_dossier",
|
\n "access_type":"index",
|
\n "key":"PRIMARY",
|
\n "key_length":"3",
|
\n "used_key_parts":[
|
"id_dossier"
|
],
|
\n "r_loops":1,
|
\n "rows":1140392,
|
\n "r_rows":662000,
|
\n "r_total_time_ms":175.43,
|
\n "filtered":100,
|
\n "r_filtered":100,
|
\n "using_index":true \n
|
} \n
|
} \n
|
} \n
|
},
|
\n "subqueries":[
|
\n {
|
\n "expression_cache":{
|
\n "state":"disabled",
|
\n "r_loops":0,
|
\n "query_block":{
|
\n "select_id":24,
|
\n "r_loops":1197381,
|
\n "r_total_time_ms":468.43,
|
\n "outer_ref_condition":"depot_banque.id_depot_banque is not null",
|
\n "table":{
|
\n "table_name":"financial_depot_banque_offre",
|
\n "access_type":"ref",
|
\n "possible_keys":[
|
"id_depot_banque"
|
],
|
\n "key":"id_depot_banque",
|
\n "key_length":"3",
|
\n "used_key_parts":[
|
"id_depot_banque"
|
],
|
\n "ref":[
|
"financial_cf.depot_banque.id_depot_banque"
|
],
|
\n "r_loops":81075,
|
\n "rows":1,
|
\n "r_rows":1.0164,
|
\n "r_total_time_ms":132.69,
|
\n "filtered":100,
|
\n "r_filtered":100,
|
\n "using_index":true \n
|
} \n
|
} \n
|
} \n
|
},
|
\n {
|
\n "expression_cache":{
|
\n "state":"disabled",
|
\n "r_loops":0,
|
\n "query_block":{
|
\n "select_id":22,
|
\n "r_loops":1196730,
|
\n "r_total_time_ms":8136.7,
|
\n "read_sorted_file":{
|
\n "r_rows":0.1782,
|
\n "filesort":{
|
\n "sort_key":"dp.montant_hib",
|
\n "r_loops":1196730,
|
\n "r_total_time_ms":3717.1,
|
\n "r_used_priority_queue":false,
|
\n "r_output_rows":0,
|
\n "r_buffer_size":"(varied across executions)",
|
\n "table":{
|
\n "table_name":"dp",
|
\n "access_type":"ref",
|
\n "possible_keys":[
|
"PRIMARY",
|
"id_dossier_refinancement"
|
],
|
\n "key":"id_dossier_refinancement",
|
\n "key_length":"3",
|
\n "used_key_parts":[
|
"id_dossier_refinancement"
|
],
|
\n "ref":[
|
"financial_cf.dr.id_dossier_refinancement"
|
],
|
\n "r_loops":1196730,
|
\n "rows":1,
|
\n "r_rows":0.22,
|
\n "r_total_time_ms":2305.1,
|
\n "filtered":100,
|
\n "r_filtered":100,
|
\n "attached_condition":"dp.id_dossier_refinancement <=> dr.id_dossier_refinancement" \n
|
} \n
|
} \n
|
},
|
\n "table":{
|
\n "table_name":"sd",
|
\n "access_type":"eq_ref",
|
\n "possible_keys":[
|
"PRIMARY",
|
"id_depot_banque",
|
"etat"
|
],
|
\n "key":"PRIMARY",
|
\n "key_length":"4",
|
\n "used_key_parts":[
|
"id_suivi_depot"
|
],
|
\n "ref":[
|
"func"
|
],
|
\n "r_loops":213211,
|
\n "rows":1,
|
\n "r_rows":1,
|
\n "r_total_time_ms":456.84,
|
\n "filtered":100,
|
\n "r_filtered":37.72,
|
\n "attached_condition":"sd.id_depot_banque = dp.id_depot_banque and sd.id_suivi_depot = (subquery#23) and sd.etat in ('accord','accord sous reserve','depot_valider')" \n
|
},
|
\n "subqueries":[
|
\n {
|
\n "expression_cache":{
|
\n "state":"disabled",
|
\n "r_loops":0,
|
\n "query_block":{
|
\n "select_id":23,
|
\n "r_loops":426422,
|
\n "r_total_time_ms":1411.5,
|
\n "table":{
|
\n "table_name":"financial_suivi_depot",
|
\n "access_type":"ref",
|
\n "possible_keys":[
|
"id_depot_banque"
|
],
|
\n "key":"id_depot_banque",
|
\n "key_length":"3",
|
\n "used_key_parts":[
|
"id_depot_banque"
|
],
|
\n "ref":[
|
"financial_cf.dp.id_depot_banque"
|
],
|
\n "r_loops":426422,
|
\n "rows":1,
|
\n "r_rows":3.5917,
|
\n "r_total_time_ms":1047.7,
|
\n "filtered":100,
|
\n "r_filtered":100,
|
\n "using_index":true \n
|
} \n
|
} \n
|
} \n
|
} \n
|
] \n
|
} \n
|
} \n
|
},
|
\n {
|
\n "expression_cache":{
|
\n "state":"disabled",
|
\n "r_loops":0,
|
\n "query_block":{
|
\n "select_id":20,
|
\n "r_loops":1172301,
|
\n "r_total_time_ms":2389.8,
|
\n "table":{
|
\n "table_name":"financial_depot_notaire",
|
\n "access_type":"ref",
|
\n "possible_keys":[
|
"id_dossier_refinancement"
|
],
|
\n "key":"id_dossier_refinancement",
|
\n "key_length":"3",
|
\n "used_key_parts":[
|
"id_dossier_refinancement"
|
],
|
\n "ref":[
|
"financial_cf.dossier.id_dossier"
|
],
|
\n "r_loops":1172301,
|
\n "rows":1,
|
\n "r_rows":0.0406,
|
\n "r_total_time_ms":1637.5,
|
\n "filtered":100,
|
\n "r_filtered":100,
|
\n "using_index":true \n
|
} \n
|
} \n
|
} \n
|
} \n
|
] \n
|
} \n
|
}
|
|