Details

    • Type: Task
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Fix Version/s: None
    • Component/s: Optimizer
    • Labels:
      None

      Description

      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

      1. # Query_time: 135.814891 Lock_time: 0.000971 Rows_sent: 1000 Rows_examined: 42567341
      2. 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
      }
      

        Attachments

          Activity

            People

            • Assignee:
              igor Igor Babaev
              Reporter:
              stephane@skysql.com VAROQUI Stephane
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated: