Comportement étrange avec les index et "order by"

[Dia_FR]

bonjour,

j'ai rencontré un comportement étrange de ma BD et après quelques tests j'ai trouvé la solution, sauf que je n'ai compris ni l'origine du problème ni le pourquoi de la solution donc si quelqu'un peut éclairer ma lanterne...

en MySQL 5, moteur InnoDB, voilà le script correspondant à la table en question, en ne gardant que le code pertinent :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE TABLE `fxc_pages` (
  `page_id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `page_code` varchar(255) collate latin1_general_ci NOT NULL,
  `site_id` int(11) UNSIGNED NOT NULL,
  `template_id` int(11) UNSIGNED NOT NULL,
  `parent_id` int(11) UNSIGNED DEFAULT NULL,
  `page_order` int(11) UNSIGNED NOT NULL DEFAULT '0',
  PRIMARY KEY  (`page_id`),
  UNIQUE KEY `page_code` (`page_code`,`site_id`),
  UNIQUE KEY `page_order` (`site_id`,`parent_id`,`page_order`),
  KEY `parent_id` (`parent_id`),
  KEY `template_id` (`template_id`),
  KEY `site_id` (`site_id`),
  CONSTRAINT `fxc_pages_parent_id` FOREIGN KEY (`parent_id`) REFERENCES `fxc_pages` (`page_id`) ON DELETE CASCADE,
  CONSTRAINT `fxc_pages_site_id` FOREIGN KEY (`site_id`) REFERENCES `fxc_sites` (`site_id`) ON DELETE CASCADE,
  CONSTRAINT `fxc_pages_template_id` FOREIGN KEY (`template_id`) REFERENCES `fxc_templates` (`template_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

donc en gros, une PK, 3 FK avec chacune un index et 2 index unique dont un portant sur 3 colonnes

avec cette structure, une requête comme celle là, le order by n'était pas pris en compte

Code :

UPDATE fxc_pages SET page_order = page_order - 1 WHERE site_id = x AND parent_id = y AND page_order <= z AND page_order > t ORDER BY page_order ASC

pour régler le problème, il a suffit de virer la clé sur site_id :

Code :

  KEY `site_id` (`site_id`),

je ne suis pas un pro MySQL et je connais les détails de sa gestion des index et des clés donc si quelqu'un sait...
merci d'avance

[CinePhil]

Euh... ça sert à quoi de faire un ORDER BY sur une requête UPDATE ?

[Dia_FR]

à l'exécuter sur les enregistrements dans un certain ordre
dans le cas que j'ai présenté j'ai une clé d'unicité sur order, site et parent
je dois changer la valeur de order, pour un site et un parent donnés
pour le faire en une seule requete sans violer l'unicité, je les ordonne par order ascendant si je dois faire order--, ou par order descendant si je dois faire order++
fin bon c de l'algo de base quoi

[Alain Defrance]

La clause UPDATE effectue un traitement ensembliste, c'est-à-dire que si tu fait :

Code :

SET un_champ = un_champ + 1

Tous les champs sont modifié en même temps, il n'y a donc pas de viol au niveau de l'intégrité.

Bien évidement dans l'exécution de la requête il dois y avoir un traitement séquentiel mais, mais le contrôle s'effectue après l'update (comme si on ouvrais une transaction avant, et on faisait un COMIT après).
D'ailleurs le DML est une transaction un court moment.

[Dia_FR]

intéressant à savoir, je ne crois pas que j'avais essayé, partant du principe que le contrôle d'intégrité se faisait à chaque modification d'un enregistrement, j'essaie ça dès lundi !

[Dia_FR]

nan ba en virant le "order by" sur l'update, ça foire bien en posant une "integrity constraint violation"

donc j'en reviens à ma question de base si quelqu'un sait...