Optimisation update dans une boucle

29 août 2007, 20:05

J'ai une table avec quelques données pour l'exemple

CREATE TABLE `images` (
  `ID` int(11) NOT NULL auto_increment,
  `rubrique` tinyint(4) NOT NULL default '0',
  `adresse` varchar(200) collate latin1_general_ci NOT NULL default '',
  `commentaire` text collate latin1_general_ci,
  `ordre` float NOT NULL default '0',
  PRIMARY KEY  (`ID`),
  KEY `rubrique` (`rubrique`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=8 ;

-- 
-- Contenu de la table `images`
-- 

INSERT INTO `images` (`ID`, `rubrique`, `adresse`, `commentaire`, `ordre`) VALUES 
(1, 1, 'sculpt01.jpg', NULL, 1),
(2, 1, 'sculpt03.jpg', NULL, 1.5),
(3, 2, 'evier01.jpg', NULL, 1),
(4, 1, 'sculpt02.jpg', NULL, 2),
(5, 2, 'evier02.jpg', NULL, 2);

et je voudrais faire un update sur la colonne 'ordre' pour obtenir le résultat

Code : Tout sélectionner

(1, 1, 'sculpt01.jpg', NULL, 1),
(2, 1, 'sculpt03.jpg', NULL, 2),
(3, 2, 'evier01.jpg', NULL, 1),
(4, 1, 'sculpt02.jpg', NULL, 3),
(5, 2, 'evier02.jpg', NULL, 2);

Actuellement je fais :

$id_rub = (isset($_GET['id_rub']) && is_numeric($_GET['id_rub']))? intval($_GET['id_rub']) : 0;
$query_Recordset1 = "SELECT ID, ordre FROM `images` WHERE rubrique = $id_rub ORDER BY ordre ASC";
$Recordset1 = mysql_query($query_Recordset1, $connexion) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$numro="1";
do
{
$update= $row_Recordset1['ID'];
$updateSQL = sprintf("UPDATE images SET ordre=%d WHERE ID=%d",$numro,$update);
$Result1 = mysql_query($updateSQL, $connexion) or die(mysql_error());
$numro++;
}while ($row_Recordset1 = mysql_fetch_assoc($Recordset1));

Ma question est : y'a t-il moyen de faire plus optimisé ?

29 août 2007, 20:44

Je ne sais pas si ca peut marcher, mais peut être en bouclant la dessus ?

Code : Tout sélectionner

SET @ord=1;
UPDATE images SET ordre=@ord, @ord=@ord+1 WHERE rubrique='n' ORDER BY ordre ASC

29 août 2007, 21:19

J'ai passé cette requête dans la fenêtre sql de phpmyadmin (Version du serveur: 5.0.27-community-log; Version du client MySQL: 5.0.22)

requête SQL:

UPDATE images SET ordre = @ord ,
@ord = @ord +1 WHERE rubrique =1 ORDER BY ordre ASC

MySQL a répondu:

#1064 - Erreur de syntaxe près de '@ord=@ord+1 WHERE rubrique=1 ORDER BY ordre ASC' à la ligne 1

29 août 2007, 21:48

Par curiosité, est-ce que tu prévois d'exécuter cette requête souvent ? parce que même optimisé ça risque d'être relativement coûteux...

Si tu aimes les solutions compliquées (pas vraiment compliquée, mais longue) tu peux le faire avec une procédure. D'ailleurs, si tu pouvais comparer les temps d'exécutions sur la table complète, ça m'intéresse.

Aussi, si tu as beaucoup d'images, pense à changer l'index sur (rubrique) en index sur (rubrique,ordre)

Code : Tout sélectionner

DELIMITER //

DROP PROCEDURE IF EXISTS reorder
//

CREATE PROCEDURE reorder()
BEGIN
	DECLARE $id INT;
	DECLARE $rubrique, $prev_rubrique TINYINT;
	DECLARE $ordre, $n FLOAT;

	DECLARE $done TINYINT DEFAULT 0;
	DECLARE $cur CURSOR FOR
		  SELECT ID, rubrique, ordre
		    FROM images
		ORDER BY rubrique, ordre;
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET $done = 1;

	OPEN $cur;
	FETCH $cur INTO $id, $rubrique, $ordre;

	WHILE (NOT $done)
	DO
		IF ($rubrique = $prev_rubrique)
		THEN
			SET $n = $n + 1;
		ELSE
			SET $n = 1;
			SET $prev_rubrique = $rubrique;
		END IF;

		IF ($ordre <> $n)
		THEN
			UPDATE images SET ordre = $n WHERE ID = $id;
		END IF;

		FETCH $cur INTO $id, $rubrique, $ordre;
	END WHILE;

	CLOSE $cur;
END
//

29 août 2007, 22:22

Effectivement Hubert, j'avais bien vu ce pb, c'est pour cela que je cherche à optimiser.

Cela dit, non, cette requête n'est utilisée que par l'administrateur pour permettre de modifier l'ordre de présentation de ses produits ou d'en insérer un nouveau à l'endroit voulu, c'est à dire rarement. Et normalement il ne devrait pas y avoir plus d'une cinquantaine de produits par rubrique.

Pour les visiteurs je fais simplement un select ... where id=... order by ordre asc

C'est certain que cette méthode simple trouverait vite ses limites pour des gros sites mais ce n'est pas ma cible.

Cela dit je suis preneur pour une méthode plus pro si c'est pas trop complexe à mettre en oeuvre.

PS : Je n'ai répondu qu'à la première partie de ton message et je viens juste de voir la suite.

29 août 2007, 22:54

En général pour ce genre de trucs le plus courant est de le faire en PHP. Avant d'insérer on s'assure qu'il n'y a pas de produit qui correspond à l'ordre du nouveau produit et on décale les produits pour lui faire une place. Lors d'un déplacement au sein d'une rubrique on décale les produits entre l'ancienne et la nouvelle position, etc... D'un autre côté, je comprends qu'on trouve plus simple de tout resynchroniser à chaque modification.

Si tu veux je peux t'écrire les TRIGGERs pour mettre à jour ta colonne ordre automatiquement, ça peut toujours servir à quelqu'un qui tombera sur ce topic par hasard.

29 août 2007, 22:58

Oui bien sûr si tu veux développer ton dernier post c'est avec plaisir, ça pourra toujours servir.

Concernant la procèdure de ton avant dernier post

je sais pas la mettre en oeuvre

29 août 2007, 23:39

Ah ben non, tiens... j'avais oublié qu'on ne pouvait pas mettre à jour une table si elle est utilisé par le TRIGGER qui invoque sa mise à jour. En clair, quand tu effaces un enregistrement de la table, tu ne peux pas avoir un TRIGGER qui met à jour les enregistrements restants. Autant dire que ça limite pas mal les possibilités...

Tu pourrais utiliser une procédure mais bon... à ce compte-là autant le faire en PHP, c'est généralement plus simple à debugger.

Pour la postérité, les TRIGGERs qui ne marchent pas (la prochaine fois je testerai le plus simple d'abord avant de tout écrire

)

Code : Tout sélectionner

DELIMITER //

DROP TRIGGER IF EXISTS ad_images
//

CREATE TRIGGER ad_images AFTER DELETE ON images
FOR EACH ROW
BEGIN
	/**
	* En cas de suppression, on décale les éléments suivants vers le haut
	*/
	UPDATE images
	   SET ordre = ordre - 1
	 WHERE rubrique = OLD.rubrique
	   AND ordre > OLD.ordre;
END
//

DROP TRIGGER IF EXISTS bu_images
//

CREATE TRIGGER bu_images BEFORE UPDATE ON images
FOR EACH ROW
BEGIN
	DECLARE $pos SMALLINT UNSIGNED;

	IF (NEW.rubrique <> OLD.rubrique)
	THEN
		/**
		* Si un élément est déplacé d'une rubrique à l'autre, on décale les éléments
		* suivants dans l'ancienne rubrique et on détermine la nouvelle position dans
		* la nouvelle rubrique
		*/
		UPDATE images
		   SET ordre = ordre - 1
		 WHERE rubrique = OLD.rubrique
		   AND ordre > OLD.ordre;

		SELECT COALESCE(MAX(ordre) + 1, 1)
		  INTO $pos
		  FROM images
		 WHERE rubrique = NEW.rubrique;

		SET NEW.ordre = $pos;

	ELSEIF (NEW.ordre <> OLD.ordre)
	THEN
		/**
		* L'élément est déplacé au sein d'une même rubrique. Par défaut, si un élément est
		* placé à la même position qu'un élément existant, il pousse cet élément et tous les
		* suivants vers le bas. On peut placer un élément en fin de liste en lui donnant une
		* valeur supérieure à celle du dernier élément
		*/
		IF (NEW.ordre < 1)
		THEN
			/**
			* On ignore les valeurs illégales
			*/
			SET NEW.ordre = OLD.ordre;

		ELSEIF (NEW.ordre < OLD.ordre)
		THEN
			/**
			* L'élément est déplacé vers le haut, les éléments entre l'ancienne et la
			* nouvelle position sont décalés vers le bas
			*/
			UPDATE images
			   SET ordre = ordre + 1
			 WHERE rubrique = NEW.rubrique
			   AND ordre BETWEEN NEW.ordre AND (OLD.ordre - 1);

		ELSEIF (NEW.ordre > OLD.ordre)
		THEN
			/**
			* L'élément est déplacé vers le bas, on récupère la position du dernier élément
			* pour s'assurer que la nouvelle valeur n'est pas hors du classement (pas de trous)
			*/
			SELECT MAX(ordre)
			  INTO $pos
			  FROM images
			 WHERE rubrique = NEW.rubrique;

			IF (NEW.ordre > $pos)
			THEN
				/**
				* L'élément est déplacé en fin de liste, on s'assure de ne pas
				* laisser de trous en le positionnant pile à la fin
				*/
				SET NEW.ordre = $pos;
			END IF;

			/**
			* On décale les éléments entre l'ancienne et la nouvelle position vers le haut
			*/
			UPDATE images
			   SET ordre = ordre - 1
			 WHERE rubrique = NEW.rubrique
			   AND ordre BETWEEN (OLD.ordre + 1) AND $pos;
		END IF;
	END IF;
END
//

DROP TRIGGER IF EXISTS bi_images
//

CREATE TRIGGER bi_images BEFORE INSERT ON images
FOR EACH ROW
BEGIN
	DECLARE $pos SMALLINT UNSIGNED;

	/**
	* On commence par récupérer la position du prochain élément, c'est à dire la position
	* d'un élément qu'on ajouterait à la liste
	*/
	SELECT COALESCE(MAX(ordre) + 1, 1)
	  INTO $pos
	  FROM images
	 WHERE rubrique = NEW.rubrique;

	IF (NEW.ordre > $pos)
	THEN
		/**
		* On s'assure de ne pas laisser de "trous" dans le classement
		*/
		SET NEW.ordre = $pos;

	ELSEIF (NEW.ordre > 0)
	THEN
		/**
		* La position de cet élément correspond à un élément existant, on décale donc cet élément
		* et tous les éléments suivants vers le bas
		*/
		UPDATE images
		   SET ordre = ordre + 1
		 WHERE rubrique = NEW.rubrique
		   AND ordre >= NEW.ordre;
	ELSE
		/**
		* Pas de position spécifiée, on rajoute l'élément à la fin
		*/
		SET NEW.ordre = $pos;
	END IF;	
END
//

30 août 2007, 00:11

(la prochaine fois je testerai le plus simple d'abord avant de tout écrire )

En même temps, à la vitesse où tu écris, je me dis que ça doit être ta langue maternelle, le sql

En tous cas merci pour la piste de traitement php. Je la mettrai en application si j'ai des besoins plus importants.

Je mets résolu car c'était plus un pb de méthode que d'optimisation de requête.

Optimisation update dans une boucle

Qui est en ligne