Discussion :

[tidou]

J'aimerais bien que tu m'expliques quelle est selon toi la différence fondamentale entre l'utilisation des index dans une clause WHERE et dans des jointures

ben tu va avoir une table catégorie de film avec comme clef primaire le numéro de la catégorie de film avec pour valeur

1
2
3

et une table film avec le numéro de film et chaque film appartient à une catégorie de film (donc une clef étrangère nommé "numéro catégorie film" sera ajouté dans la table film. Les valeurs dans la table film pour le champ numéro de film et numéro catégorie film pourraient être :

N° de film 1 et comme catégorie de film 1
N° de film 2 et comme catégorie de film 1
N° de film 3 et comme catégorie de film 1
N° de film 4 et comme catégorie de film 1
N° de film 5 et comme catégorie de film 2
N° de film 6 et comme catégorie de film 2
N° de film 7 et comme catégorie de film 1

si tu créer un index sur catégorie de film et le numéro de film en même temps le SBGD te créera un index de ce type (je pense car je n'ai pas étudié les index sous MySQL) :

Num catégorie | Num Film
1 | 1
1 | 2
1 | 3
1 | 4
1 | 7
2 | 5
2 | 6

en faisant une jointure entre catégorie et film tu évitera au SBGD de : rechercher quels sont les films qui appartiennent à chaque catégorie et de trier les résultats car ce travail est déja fait dans l'index. Le SBGD n'aura alors qu'à séléctionner les valeurs dans l'index sans avoir à trier ou balayer dans tous les sens ce qui est donc plus rapide en cas de sélection avec des jointures mais sera plus lent lors des insertions ou modification qui touchent à une valeur de l'index car l'index devra alors être modifié.

Ou bien encore imagine qu'on veut uniquement les films dont la catégorie d'appartenance est égal à 1 : le SBGD n'aura qu'à parcourir l'index jusque la catégorie soit différente de 1 (vu que l'index est trié) pour arrêter le balayage de l'index ce qui est bien plus rapide que de balayer séquentiellement toutes les valeurs du champ catégorie de film de la table film à la recherche d'un film avec pour catégorie de film le numéro 1 !

Voilà

[ren0]

si je comprends bien une solution radicale pour échapper à ce BUG (car apparemment c'est un bug entre MySQL et FreeBSD) ce serait de passer à FreeBSD 5

oui, mais comme tu l'indique c'est radical...
je pense qu'in vaut mieux essayer d'investiguer encore un peu avant de te lancer dans un upgrade os.

pour --skip-name-resolve, il faut le rajouter dans ta commande de démarrage de mysqld

[Bluelane]

En gros qu'est ce que ça va changer cette commande ? A quoi elle sert ?

[ren0]

D'aprés ce que j'ai lu, l'implemémentation de la recherche DNS est buggée sous FreeBSD 4xxx.
la commande --skip-name-resolve évite à MySQL de faire des requetes DNS pour résoudre les noms de machines lors de connexions. Donc évite de faire appel à des fonctions buggées.
Dans certain cas que j'ai vu, cette simple option au démarrage à résolu le problème de charge intempestive. Mais malheureusement pas dans tous les cas, donc il n'y a aucune garantie.
Mais tu ne prends aucun risque en essayant .

[Bluelane]

Mais cette fonction n'a pourtant pas une utilité ?

[ren0]

Si, par exemple de résoudre le nom d'une machine (de donner son IP) si tu donne les permissions à un utilisateur depuis une machine X expressement nommé et pas par l'IP.
du style :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

GRANT ALL PRIVILEGES ON test.* TO 'toto'@'machineX'

je vois pas d'autre utilité à part peut-être dans le cadre de la réplication, mais je ne pense que ce soit ton cas.
il y en a peut-être d'autre, c'est vendredi, j'ai du mal...
quelqu'un voit un autre cas ?

[Bluelane]

L'IP me sert beaucoup dans mes scripts car les utilisateurs sont bloqués (quand ils ont fait des conneries) temporairement grâce à l'IP. Et ça nous permet de rapprocher les faux comptes.

[ren0]

oui, dsl, je m'exprime mal.
si tu fonctionne avec des IP, pas de probleme.
il peut y avoir probleme si tu utilise des noms de machines.

pas de soucis pour les IP.

tu as un serveur de test sur lequel tu peux reproduire ça, ou tu bosse direct en prod ?

[Bluelane]

Je bosse directement en production, par contre ici on a un serveur local sur lequel on test des développements mais c'est un MySQL qui tourne sous Windows XP Pro.

[ren0]

fait le test en dev sous XP avec l'option de démarrage --skip-name-resolve. Si comme je le pressens RAS, alors fais de même en prod en surveillant le comportement pendant qq jours....

[Bluelane]

Le problème c'est que sur notre serveur Local la surconsommation MySQL n'apparait pas. Ca ne le fait que depuis le serveur sous FreeBSD. On avait tenté l'oppération en y important toutes les bases de données la même configuration de Apache, MySQL et PHP.

[Maximil ian]

Num catégorie | Num Film
1 | 1
1 | 2
1 | 3
1 | 4
1 | 7
2 | 5
2 | 6

en faisant une jointure entre catégorie et film tu évitera au SBGD de : rechercher quels sont les films qui appartiennent à chaque catégorie et de trier les résultats car ce travail est déja fait dans l'index. Le SBGD n'aura alors qu'à séléctionner les valeurs dans l'index sans avoir à trier ou balayer dans tous les sens ce qui est donc plus rapide en cas de sélection avec des jointures mais sera plus lent lors des insertions ou modification qui touchent à une valeur de l'index car l'index devra alors être modifié.

Ou bien encore imagine qu'on veut uniquement les films dont la catégorie d'appartenance est égal à 1 : le SBGD n'aura qu'à parcourir l'index jusque la catégorie soit différente de 1 (vu que l'index est trié) pour arrêter le balayage de l'index ce qui est bien plus rapide que de balayer séquentiellement toutes les valeurs du champ catégorie de film de la table film à la recherche d'un film avec pour catégorie de film le numéro 1 !

Il me semble que tu confonds deux concepts séparés : l'intégrité référentielle, mise en oeuvre à travers des FOREIGN KEYS, et les index.

Un index recense pour chaque valeur distincte d'une colonne les positions des lignes correspondantes dans le fichier de données de la table.
En reprenant ton exemple : un index sur film.catégorie indiquera par exemple que les lignes de la table film ayant une catégorie égale à 1 se trouvent aux adresses (x, y, z) dans les données de la table, ceux de la catégorie 2 aux adresses (k, w), etc. Cela permet d'accéder directement aux emplacements des lignes recherchées sans tout passer en revue.

Les clés étrangères quant à elles ne sont là que pour appliquer les règles d'intégrité référentielle, elles n'apportent aucune performance supplémentaire :

Note that foreign keys in SQL are used to check and enforce referential integrity, not to join tables.

(http://dev.mysql.com/doc/refman/5.0/...eign-keys.html)

Ton tableau décrit peut-être bien la représentation interne des clés étrangères mais pas celle des index.
Pour accélérer une jointure (comme toute autre requête effectuant une recherche), il faut un index, une foreign key ne suffit pas. D'ailleurs au passage certains ont demandé que MySQL crée l'index automatiquement en même temps que la clé étrangère : http://bugs.mysql.com/bug.php?id=3325.

Voir aussi http://sqlpro.developpez.com/cours/sqlaz/ddl/?page=partie2#L10

[Maximil ian]

Bluelane, as-tu résolu ton problème ?

Je ne sais pas si tu utilises le moteur InnoDB mais dans le cas contraire il serait peut-être intéressant de le désactiver (paramètre skip-innodb dans le fichier de conf).

[Bluelane]

Bonjour,

En quelques sortes... En fait nous passons la semaine prochaine sur un nouveau serveur, un dédié avec Debian... Ce qui devrait radicalement solutionner le problème de bug entre FreeBSD et MySQL !

Nous qui pensions que FreeBSD était stable !!

[Maximil ian]

OK.

Bon courage et si tu repasses sur le forum, tiens-nous au courant du dénouement de l'affaire

[Bluelane]

Merci beaucoup !

En tous cas je tiens à remercier tous ceux qui sont invervenu sur ce topic car ça m'a vraiment aidé !

C'est un forum génial et c'est vraiment sympa de pouvoir partager des connaissances ensemble..