Problématique de performance

Considérons cette requête SQL où les champs bizCustomerIncoming_id (requête externe) et cid (sous-requête) sont endexés :

DELETE FROM customer_path 
WHERE customer_id IN (
  SELECT identifier FROM customer_data 
  WHERE client_ref = '315upfdv34umngfrxxxxxx'
);

Malgré les index, l'exécution nécessite un parcours complet de table. Examinons les stratégies d'optimisation.

Stratégies d'optimisation

L'optimiseur MySQL utilise différentes approches selon le type de sous-requête :

Requêtes IN/=ANY

• Semi-jointure
• Matérialisation
• Transformation EXISTS

Requêtes NOT IN/<>ALL

• Matérialisation
• Transformation EXISTS

Tables dérivées

• Fusion avec la requête principale (derived_merge)
• Matériailsation en table temporaire

⚠️ Les sous-requêtes dans les UDPATE/DELETE ne peuvent utiliser ni semi-jointure ni matérialisation.

Mécanismes d'exécution

Prenons l'exemple avec deux tables :

CREATE TABLE source (
  id INT PRIMARY KEY,
  col_x INT,
  col_y INT,
  INDEX idx_x (col_x)
);

CREATE TABLE target LIKE source;
INSERT INTO target SELECT * FROM source WHERE id <= 100;

-- Requête exemple :
SELECT * FROM target 
WHERE col_x IN (SELECT col_y FROM source WHERE id < 10);

MySQL transforme cette requête en :

SELECT * FROM target 
WHERE EXISTS (
  SELECT 1 FROM source 
  WHERE id < 10 AND source.col_y = target.col_x
);

Plan d'exécution typique :

+----+--------------------+--------+------+---------------+------+---------+-------------+
| id | select_type        | table  | type | key           | rows | filtered | Extra       |
+----+--------------------+--------+------+---------------+------+---------+-------------+
| 1  | PRIMARY            | target | ALL  | NULL          | 100  | 100.00  | Using where |
| 2  | DEPENDENT SUBQUERY | source | range| PRIMARY       | 9    | 10.00   | Using where |
+----+--------------------+--------+------+---------------+------+---------+-------------+

Processus :

1. Parcourir chaque ligne R de target
2. Exécuter la sous-requête pour col_x de R
3. Conserver R si la sous-requête retourne vrai

Semi-jointure

Alternative efficace utilisant une jointure interne :

SELECT target.* 
FROM target JOIN source 
ON target.col_x = source.col_y 
WHERE source.id < 10;

Plan d'exécution optimisé :

+----+--------------+-------------+-------+---------------+---------+------+-------------+
| id | select_type  | table       | type  | key           | ref     | rows | Extra       |
+----+--------------+-------------+-------+---------------+---------+------+-------------+
| 1  | SIMPLE       | <subquery2>| ALL   | NULL          | NULL    | NULL | Using where |
| 1  | SIMPLE       | target      | ref   | idx_x         | col_y   | 1    | NULL        |
| 2  | MATERIALIZED | source      | range | PRIMARY       | NULL    | 9    | Using where |
+----+--------------+-------------+-------+---------------+---------+------+-------------+

Processus optimisé :

1. Exécuter la sous-requête et stocker les résultats distincts
2. Parcourir les résultats temporaires
3. Rechercher les correspondances dans target

Matérialisation

Création d'une table temporaire :

+----+-------------+--------+------+---------------+------+------+-------------+
| id | select_type | table  | type | key           | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+------+-------------+
| 1  | PRIMARY     | target | ALL  | NULL          | NULL | 100  | Using where |
| 2  | SUBQUERY    | source | range| PRIMARY       | NULL | 9    | Using where |
+----+-------------+--------+------+---------------+------+------+-------------+

Résolution du problème initial

La requête DELETE originale utilise une stratégie EXISTS avec parcours complet car :

• Les sous-requêtes dans DELETE ne peuvent utiliser ni semi-jointure ni matérialisation
• L'optimiseur doit scanner toute la table pour appliquer la condition

Solution optimale :

DELETE customer_path
FROM customer_path 
JOIN customer_data ON customer_path.customer_id = customer_data.identifier
WHERE customer_data.client_ref = '7Ex46Dz22Fqq6iuPCLPlzQ';