Discussion :

[Sylvercis]

Bonjour,

j'ai un problème de vitesse sur une requete, pouvez vous m'aider?

voici mes tables:

Code mysql :

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

1
2
3
4
5
6
 
CREATE TABLE `site_domaines` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id du (sous)domaine',
 `nom` varchar(10) NOT NULL COMMENT 'nom du domaine',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COMMENT='Cette table contient la liste des (sous)domaine du site'

Code mysql :

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

1
2
3
4
5
6
7
 
CREATE TABLE `site_sections` (
 `domaine_id` int(10) unsigned NOT NULL COMMENT 'id du (sous)domaine',
 `section_id` int(10) unsigned NOT NULL COMMENT 'id de la section',
 `nom` varchar(20) NOT NULL COMMENT 'nom de la section',
 PRIMARY KEY (`domaine_id`,`section_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Cette table contient la liste des sections du site'

Code mysql :

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

1
2
3
4
5
6
7
8
9
10
 
CREATE TABLE `site_urls` (
 `domaine_id` int(10) unsigned NOT NULL COMMENT 'id du (sous)domaine',
 `section_id` int(10) unsigned NOT NULL COMMENT 'id de la section dans le domaine',
 `item_id` int(10) unsigned NOT NULL COMMENT 'id de l''element dans la section',
 `language_id` int(10) unsigned NOT NULL COMMENT 'id du language de l''url',
 `url` text NOT NULL COMMENT 'url de la page',
 `modified` datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'date de mise à jour de l''article',
 PRIMARY KEY (`domaine_id`,`section_id`,`item_id`,`language_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Cette table contient toutes les urls du site'

item_id sert à lier les urls d'un meme article en plusieurs langues
la table site_urls contient toutes les urls du site pour les pages en français et les pages en anglais

ET voici la requete:

Code mysql :

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

1
2
3
4
5
6
7
8
 
SELECT sufr.url fr, suen.url en, su.modified
FROM site_urls su
LEFT JOIN site_urls sufr ON sufr.domaine_id = su.domaine_id AND sufr.section_id = su.section_id AND sufr.item_id = su.item_id AND sufr.language_id = 1 
LEFT JOIN site_urls suen ON sufr.domaine_id = su.domaine_id AND suen.section_id = su.section_id AND suen.item_id = su.item_id AND suen.language_id = 2 
WHERE su.domaine_id = 1 AND su.section_id = 2 
GROUP BY su.item_id
ORDER BY su.modified DESC

Je cherche a obtenir la liste des urls d'une section du site avec les colonnes suivantes:

fr = url de la page en français,
en = url de la page en anglais,
modified = date de dernière modification (du couple français anglais).


La requete fonctionne correctement il me semble mais il me faut 27 secondes pour sortir 5000 lignes, je m'attendais a avoir un resultat en moins de 3 secondes.

Avez vous une idée du problème?

Merci d'avance

[tatayo]

Bonjour,
Le schéma de ta base me laisse perplexe, mais passons.
Je vois plusieurs problèmes dans ta requête: pour chaque ligne de "su" peut très bien se retrouver dans une des jointures.
C'est à dire qu'une ligne avec "su.domaine_id = 1 AND su.section_id = 2" peut très bien être jointe avec elle même, vu qu'elle n'est pas filtré dans les jointures.
Si sa colonne language_id = 1, elle se retrouve dans sufr, si language_id = 2 elle se retrouve dans suen.
Donc chaque ligne est jointe au moins une fois avec elle-même.

Enfin ton critère de regroupement n'est pas correct (même si MySql laisse passer), il n'y a aucune fonction d'agrégation, et su.item_id apparaît pas dans la clause Select.

Bref il y a pas mal de chose à revoir.

Tatayo.

[SQLpro]

Bonjour,
Le schéma de ta base me laisse perplexe, mais passons.

Perplexe ???? Tu veut dire merdique ! C'est les bases hiérarchique que l'on faisait avec le cobol dans les années 60. Donc des performances de merde de façon naturelle sans se forcer.....

Extrait du livre sur SQL que je suis en train d'écrire :


A +

[Sylvercis]

@Tatayo
Au départ je voulais faire 2 select (français/anglais), les fusionner puis extraire les resultats a l'aide de:

Code mysql :

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

1
2
3
4
5
6
 
SELECT ....  
FROM
(
) UNION (
)

et je me suis dis que ça allait ramer et qu'avec des jointures ça fonctionnerai mieux mais apparemment je m'y prends mal.

Je comprends ta réponse sur le fond mais pas dans le détail.

Je ne connais pas les fonctions d’agrégation et je ne vois pas nom plus comment changer le critère de regroupement.

Je pensais plus que c'était un problème d'indexation.


@SQLpro
les 2 premières tables ne sont pas utilisées dans la requête et je ne vois pas le problème qu'il pourrait y avoir avec (je les ai mis ici pour vous aider a mieux comprendre la structure).

Pour la table site_urls, je suis d'accord qu'il y a un problème avec la clé primaire qui ne devrait contenir que 2 colonnes:
-numéro article du site
-langage_id

il me faudrai alors des tables intermediaires ou de liaison pour definir ce numéro d'article unique.
sachant que une page à 4 numéros pour l'identifier

-domaine (1 ou 2 pour le moment)
-section (1 à 10? dans le domaine)
-article (1 à xxxxx dans la section elle même dans un domaine)
-langage (1 à 2 pour le moment)


Cette table sert a répertorier les urls et non pas a définir les urls du site, je ne peux donc pas simplifier en ne donnant qu'un numéro d'article global.
Je dois partir des 4 colonnes ci dessus pour creer ce numéro de page globale et je dois ensuite pouvoir filtrer parmi cette liste d'url pour selectionner une section d'un domaine.

Ces tables me servent à generer des fichiers sitemap
et aussi à trouver/definir l'url de redirection lorsque je change de langage sur le site.

@tous
pouvez vous me donner un indice comme par exemple le nombre de tables nécessaires pour organiser ces données de façon a pouvoir les sortir par la suite par section et par langage dans des bonnes conditions?

[Sylvercis]

bon j'ai réécrit la requête pour limiter le nombre de données a traiter:

Code mysql :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
SELECT sufr.url fr, suen.url en, su.modified
FROM site_urls su
LEFT JOIN (
    SELECT domaine_id, section_id, item_id, url 
    FROM site_urls
    WHERE domaine_id = 1 AND section_id = 2 AND language_id = 1
) sufr ON sufr.domaine_id = su.domaine_id AND sufr.section_id = su.section_id AND sufr.item_id = su.item_id
LEFT JOIN (
    SELECT domaine_id, section_id, item_id, url 
    FROM site_urls
    WHERE domaine_id = 1 AND section_id = 2 AND language_id = 2
) suen ON suen.domaine_id = su.domaine_id AND suen.section_id = su.section_id AND suen.item_id = su.item_id
WHERE su.domaine_id = 1 AND su.section_id = 2 
GROUP BY su.item_id
ORDER BY su.modified DESC

j'arrive a un résultat en 0.06s (5000 lignes)

On peut dire que ça fonctionne.

Malgré tout, vu les critiques je reste ouvert aux propositions pour m'aider a améliorer mon organisation des données et/ou une correction de la requête.

[SQLpro]

bon j'ai réécrit la requête pour limiter le nombre de données a traiter:
...
j'arrive a un résultat en 0.06s (5000 lignes)

On peut dire que ça fonctionne.

Malgré tout, vu les critiques je reste ouvert aux propositions pour m'aider a améliorer mon organisation des données et/ou une correction de la requête.

Pour faire un test censé il faut une certaine volumétrie.... Si la volumétrie est trop faible tout se passe en mémoire et les performances apparaissent correctes.... Personnellement j'estime que les données de la base devrait faire au strict minium 3 fois la taille de la RAM...

Extrait de mon nouveau livre à paraître sur SQL :


A +

[Sylvercis]

Merci pour l'info, c'est bon a savoir.

Ma requête fonctionne et c'est tout ce que je lui demande pour le moment.
Maintenant je n'en suis pas fier car je l'ai bricolé pour qu'elle passe sans être sur d'utiliser la meilleur méthode.

J'ai essayé d'utiliser la commande EXPLAIN sur mes requetes mais le resultat reçu ne m'aide pas beaucoup.
Je pense qu'il faudrai un livre remplis d'exemples d'erreurs et leurs solutions pour bien comprendre cette commande, ou bien d'avoir un diagramme en sortie plutot qu'une simple table.

Le test en surchargeant la RAM est valable pour comparer la vitesse entre plusieurs requêtes mais pas pour indiquer le bon fonctionnement d'une seule requête.
L'idéal est déjà de bien écrire la requête et bien structurer la BDD au départ, le test ne sert qu'a valider le tout ou bien a aider a choisir entre plusieurs méthodes possibles.

Donc a mon avis l'étape avant le test est déjà de passer par EXPLAIN, mais bizarrement je ne vois jamais de sujets résolu en utilisant cette commande.

[SQLpro]

EXPLAIN permet d'afficher le plan d'exécution, c'est à dire ce que le SGBD va créer comme programme informatique pour traiter les données afin de vous restituer un résultat. Le code SQL que vous avez écrit n'est pas un code exécuté. Ce n'est pas un code impératif mais plus exactement un code indicatif. Il indique ce que vous voulez obtenir comme résultat et non comment y parvenir....
En revanche le plan d'exécution fournit pas EXPLAIN, montre comment le SGBD va parvenir à vous fournir un résultat...

Maintenant il faut savoir lire un plan de requête pour en tirer quelques avantages afin d'optimiser le fonctionnement de la base. Mais dans MySQmerde, les moyens sont plus que limité, là ou dans d'autres SGBDR (par exemple SQL Server) il vous fournit les moyens d'optimiser en vous conseillant les index à poser...

Exemples :



Avec SQL Server management Studio

La même requête avec Sentry Plan Explorer :



A +

[Sylvercis]

Effectivement, c'est bien plus complet que le résultat fourni par mysql.
Malheureusement ce n'est pas accessible sur les hébergements d'entrée de gamme.

[SQLpro]

Si... Vous avez la version Web, dédiée aux sites web, qui est en mode locatif. On trouve des hébergements à partir de 23 € par mois, comme chez OVH :
https://www.ovh.com/fr/serveurs_dedi...sql_server.xml

A +

[Sylvercis]

Ok, j'avais cherché en tout inclus et pas juste un hebergement SGBD.
C'est juste une licence a ce prix là?
Faut il en plus payer la location d'un serveur dédié?

Pour le moment le site ne nous rapporte rien et il tourne correctement avec l'hebergement actuellement utilisé.
Mais je note quand même le conseil de nous tourner vers une SGBD sur un serveur dédié avec SQL Server lorsque nous aurons le budget et surtout le besoin.

[SQLpro]

Non, c'est tout compris :
serveur virtuel système + SQL Server éditions web.

A +