CODEWITHSUNDEEP
mysqlsql
Léo Coletta
Léo Coletta

Reputation: 1269

Query blocking entirely MySQL server

I try to a run a specific query. However, when I execute it, the MySQL server doesn't respond anymore.

There is approximately 30000 rows in the table base_contrats_actifs but I don't know if this is a problem.

Here is the query :

UPDATE
  base_contrats_actifs a
SET
  a.code_indice = (
    SELECT
      MAX(g.code_indice)
    FROM
      base_gid g
    WHERE
      a.num_version = g.num_version_contrat
  ),
  a.flag_bailleur_locataire = (
    SELECT
      MAX(g.flag_bailleur_locataire)
    FROM
      base_gid g
    WHERE
      a.num_version = g.num_version_contrat
  ),
  a.compte_client = (
    SELECT
      MAX(g.compte_client)
    FROM
      base_gid g
    WHERE
      a.num_version = g.num_version_contrat
  )

Can you see if there is an error ? If not, is there any way to debug the query ?

Upvotes: 2

Views: 68

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

I don't know exactly why your update is non performant, but given the number of correlated subqueries you have, I'm not surprised. Try rewriting it as an update join:

UPDATE base_contrats_actifs a
INNER JOIN
(
    SELECT
        num_version_contrat,
        MAX(code_indice)             AS max_code_indice,
        MAX(flag_bailleur_locataire) AS max_flag_bailleur_locataire,
        MAX(compte_client)           AS max_compte_client
    FROM base_gid
    GROUP BY num_version_contrat
) g
    ON a.num_version = g.num_version_contrat
SET
    a.code_indice             = g.max_code_indice,
    a.flag_bailleur_locataire = g.max_flag_bailleur_locataire,
    a.compte_client           = g.max_compte_client;

Upvotes: 2

Related Questions