Bonjour,

Je travaille sur plusieurs sites web wordpress à partir d'un thème commun. world.smart-shopping.com (DB=54,9 Mo) et mes-bons-plans.be (DB=14,6 Mo). La même requête sur chaque site ne me prend pas le même temps au point que celle sur le be prend moins de 0,5 s alors que celle sur world peut prendre jusqu'à 5 s. Certes la base est plus grande mais ça rend le site world presque inaccessible.

Voici la requête (celle sur world) :

SELECT SQL_CALC_FOUND_ROWS wpsmcp_posts.ID FROM wpsmcp_posts INNER JOIN wpsmcp_postmeta ON ( wpsmcp_posts.ID = wpsmcp_postmeta.post_id ) INNER JOIN wpsmcp_postmeta AS mt1 ON ( wpsmcp_posts.ID = mt1.post_id ) WHERE 1=1 AND ( wpsmcp_postmeta.meta_key = '_wpc_expires' AND ( ( mt1.meta_key = '_wpc_expires' AND CAST(mt1.meta_value AS SIGNED) BETWEEN '1546946077' AND '1547205277' ) ) ) AND wpsmcp_posts.post_type = 'coupon' AND (wpsmcp_posts.post_status = 'publish' OR wpsmcp_posts.post_status = 'private') GROUP BY wpsmcp_posts.ID ORDER BY wpsmcp_postmeta.meta_value+0 ASC LIMIT 0, 6

J'ai exécuté l'explain mais là je suis limité sur mes connaissances en sql : .be :

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "38629.34"
    },
    "ordering_operation": {
      "using_filesort": true,
      "grouping_operation": {
        "using_temporary_table": true,
        "using_filesort": false,
        "nested_loop": [
          {
            "table": {
              "table_name": "wpsmbe_postmeta",
              "access_type": "ref",
              "possible_keys": [
                "post_id",
                "meta_key"
              ],
              "key": "meta_key",
              "used_key_parts": [
                "meta_key"
              ],
              "key_length": "767",
              "ref": [
                "const"
              ],
              "rows_examined_per_scan": 10433,
              "rows_produced_per_join": 10433,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "6075.00",
                "eval_cost": "2086.60",
                "prefix_cost": "8161.60",
                "data_read_per_join": "10M"
              },
              "used_columns": [
                "post_id",
                "meta_key",
                "meta_value"
              ],
              "attached_condition": "((`*_wpsmbe`.`wpsmbe_postmeta`.`meta_key` <=> '_wpc_expires') and (`*_wpsmbe`.`wpsmbe_postmeta`.`meta_key` = '_wpc_expires'))"
            }
          },
          {
            "table": {
              "table_name": "wpsmbe_posts",
              "access_type": "eq_ref",
              "possible_keys": [
                "PRIMARY",
                "type_status_date"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "ID"
              ],
              "key_length": "8",
              "ref": [
                "*_wpsmbe.wpsmbe_postmeta.post_id"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 1150,
              "filtered": "11.03",
              "cost_info": {
                "read_cost": "10433.00",
                "eval_cost": "230.10",
                "prefix_cost": "20681.20",
                "data_read_per_join": "4M"
              },
              "used_columns": [
                "ID",
                "post_status",
                "post_type"
              ],
              "attached_condition": "((`*_wpsmbe`.`wpsmbe_posts`.`post_type` = 'coupon') and (`*_wpsmbe`.`wpsmbe_posts`.`post_status` = 'publish'))"
            }
          },
          {
            "table": {
              "table_name": "mt1",
              "access_type": "ref",
              "possible_keys": [
                "post_id",
                "meta_key"
              ],
              "key": "post_id",
              "used_key_parts": [
                "post_id"
              ],
              "key_length": "8",
              "ref": [
                "*_wpsmbe.wpsmbe_postmeta.post_id"
              ],
              "rows_examined_per_scan": 13,
              "rows_produced_per_join": 1044,
              "filtered": "6.99",
              "cost_info": {
                "read_cost": "14956.78",
                "eval_cost": "208.95",
                "prefix_cost": "38629.34",
                "data_read_per_join": "1M"
              },
              "used_columns": [
                "post_id",
                "meta_key",
                "meta_value"
              ],
              "attached_condition": "((`*_wpsmbe`.`mt1`.`meta_key` = '_wpc_expires') and (cast(`*_wpsmbe`.`mt1`.`meta_value` as signed) between '1547029897' and '1547289097'))"
            }
          }
        ]
      }
    }
  }
} |

world :

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "439929.51"
    },
    "ordering_operation": {
      "using_filesort": true,
      "grouping_operation": {
        "using_temporary_table": true,
        "using_filesort": false,
        "nested_loop": [
          {
            "table": {
              "table_name": "wpsmcp_postmeta",
              "access_type": "ref",
              "possible_keys": [
                "post_id",
                "meta_key"
              ],
              "key": "meta_key",
              "used_key_parts": [
                "meta_key"
              ],
              "key_length": "767",
              "ref": [
                "const"
              ],
              "rows_examined_per_scan": 34921,
              "rows_produced_per_join": 34921,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "24303.00",
                "eval_cost": "6984.20",
                "prefix_cost": "31287.20",
                "data_read_per_join": "35M"
              },
              "used_columns": [
                "post_id",
                "meta_key",
                "meta_value"
              ],
              "attached_condition": "((`*_wpsmcp`.`wpsmcp_postmeta`.`meta_key` <=> '_wpc_expires') and (`*_wpsmcp`.`wpsmcp_postmeta`.`meta_key` = '_wpc_expires'))"
            }
          },
          {
            "table": {
              "table_name": "wpsmcp_posts",
              "access_type": "eq_ref",
              "possible_keys": [
                "PRIMARY",
                "type_status_date"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "ID"
              ],
              "key_length": "8",
              "ref": [
                "*_wpsmcp.wpsmcp_postmeta.post_id"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 27783,
              "filtered": "79.56",
              "cost_info": {
                "read_cost": "34921.00",
                "eval_cost": "5556.62",
                "prefix_cost": "73192.40",
                "data_read_per_join": "97M"
              },
              "used_columns": [
                "ID",
                "post_status",
                "post_type"
              ],
              "attached_condition": "((`*_wpsmcp`.`wpsmcp_posts`.`post_type` = 'coupon') and ((`*_wpsmcp`.`wpsmcp_posts`.`post_status` = 'publish') or (`*_wpsmcp`.`wpsmcp_posts`.`post_status` = 'private')))"
            }
          },
          {
            "table": {
              "table_name": "mt1",
              "access_type": "ref",
              "possible_keys": [
                "post_id",
                "meta_key"
              ],
              "key": "post_id",
              "used_key_parts": [
                "post_id"
              ],
              "key_length": "8",
              "ref": [
                "*_wpsmcp.wpsmcp_postmeta.post_id"
              ],
              "rows_examined_per_scan": 11,
              "rows_produced_per_join": 24298,
              "filtered": "7.95",
              "cost_info": {
                "read_cost": "305614.26",
                "eval_cost": "4859.76",
                "prefix_cost": "439929.51",
                "data_read_per_join": "24M"
              },
              "used_columns": [
                "post_id",
                "meta_key",
                "meta_value"
              ],
              "attached_condition": "((`*_wpsmcp`.`mt1`.`meta_key` = '_wpc_expires') and (cast(`*_wpsmcp`.`mt1`.`meta_value` as signed) between '1547025281' and '1547284481'))"
            }
          }
        ]
      }
    }
  }
} |

Pourriez-vous m'aiguiller quant aux potentiels modifs d'index à effectuer... ou autre...

Merci d'avance.

Bonjour,

La base de données de WordPress n'est pas un exemple d'optimisation ... elle est flexible mais pas performante ... Il faut l'entretenir pour maintenir de bons temps de réponse ... Sur la table des postmeta, le ménage est assez facile à faire, tu peux avoir des doublons post_id/meta_key à supprimer.

Maintenant, si l'on regarde ta requête, il n'y a pas grand chose à faire ... remplacer le GROUP BY par un DISTINCT ...

Ce que tu veux, c'est bien "Les 6 prochains coupons publiés ou privés arrivant à expiration dans les deux prochains jours" ?

"You saved my day" comme on dit sur les forums anglophones. Cette requête affiche en effet les derniers coupons arrivant à expiration. Et ce résultat apparait dans des onglets qui d'après les stats du site n'intéressent personne. Etant donnée que la requête semble optimisée, en supprimant l'onglet "Ending soon", le site apparait très rapidement en local. Je vais donc certainement supprimer les onglets sur le site. Merci pour ton aide.