Discussion :

[TryHarder]

Bonjour,

J'ai des requêtes qui peuvent atteindre 10-15sec pour "seulement" 100 000 lignes dans la table. Deux problèmes, je dois pouvoir conditionner/filtrer la requête (dans ma clause WHERE donc) sur quasiment n'importe quelle colonne et j'ai un UNION qui reprend les mêmes données pour une période différente, ce qui alourdit d'avantage les choses.
Des index sont posés sur les plus fréquemment utilisés mais il me semble que des indexes sur toutes les colonnes seraient contre productif, sachant que cette base est quotidiennement utilisée en écriture (entre 500 et 1000 lignes supplémentaires / jour).

L'objet du déli (simplifié) :

Code :

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
17
18
19
20
21
22
23
24
25
26
27
28
29
30
 
SELECT
COUNT(*) AS "TOTAL",
ROUND(AVG(IF(R1!="0",R1-1,NULL)),1) AS "R1",
SUM(IF(`R1`!="0",1,0)) AS "R1E[]",
STD(NULLIF(`R1` ,0)) AS "R1D1",
ROUND((SUM(IF(`R1` IN("10","11"), 1,0)) / SUM(IF(`R1` > 0, 1,0)) - SUM(IF(`R1` IN("1","2","3","4","5","6","7"), 1,0)) / SUM(IF(`R1` > 0, 1,0))) * 100,0) as "RES",
ROUND(AVG(IF(`R2`!="0",R2,NULL)),1) AS "R2",
SUM(IF(`R2`!="0",1,0)) AS "R2E[]",
STD(NULLIF(`R2` ,0)) AS "R2D1",
ROUND(AVG(IF(`R3`!="0",R3,NULL)),1) AS "R3",
SUM(IF(`R3`!="0",1,0)) AS "R3E[]"
FROM `mytable`
WHERE `validation` BETWEEN TIMESTAMP("2017-01-01 00:00:00") AND TIMESTAMP("2017-06-15 23:59:59")
AND ((`RDT` BETWEEN '2017/04/01 00:00:00' AND '2017/04/30 23:59:59') OR (`RDT` BETWEEN '2017/03/01 00:00:00' AND '2017/03/31 23:59:59'))
UNION
SELECT
COUNT(*) AS "TOTAL",
ROUND(AVG(IF(R1!="0",R1-1,NULL)),1) AS "R1",
SUM(IF(`R1`!="0",1,0)) AS "R1E[]",
STD(NULLIF(`R1` ,0)) AS "R1D1",
ROUND((SUM(IF(`R1` IN("10","11"), 1,0)) / SUM(IF(`R1` > 0, 1,0)) - SUM(IF(`R1` IN("1","2","3","4","5","6","7"), 1,0)) / SUM(IF(`R1` > 0, 1,0))) * 100,0) as "RES",
ROUND(AVG(IF(`R2`!="0",R2,NULL)),1) AS "R2",
SUM(IF(`R2`!="0",1,0)) AS "R2E[]",
STD(NULLIF(`R2` ,0)) AS "R2D1",
ROUND(AVG(IF(`R3`!="0",R3,NULL)),1) AS "R3",
SUM(IF(`R3`!="0",1,0)) AS "R3E[]"
FROM `mytable`
WHERE `validation` BETWEEN TIMESTAMP("2017-01-01 00:00:00") AND TIMESTAMP("2017-06-15 23:59:59")
AND ((`RDT` BETWEEN '2017/06/01 00:00:00' AND '2017/06/30 23:59:59') OR (`RDT` BETWEEN '2017/05/01 00:00:00' AND '2017/05/31 23:59:59'))

Quand je dis simplifiée ça signifie en fait que j'ai beaucoup plus de colonnes traitées dans cette requête l'équivalent de :

Code :

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

1
2
3
4
 
ROUND(AVG(IF(`R2`!="0",R2,NULL)),1) AS "R2",
SUM(IF(`R2`!="0",1,0)) AS "R2E[]",
STD(NULLIF(`R2` ,0)) AS "R2D1",

se répète pour environ 70 à 120 colonnes, selon la table. A ce stade je bloque pour accélérer les choses, hormis un upgrade matériel qui n'est probablement pas la plus pertinente des solutions même si elle en fait peut-être parti.

Si une âme charitable a quelques conseils, des pistes voir des solutions... Merci d'avançe.

[SQLpro]

70 à 120 colonnes dans une même table c'est une vaste connerie.... Vous utilisez une base de données à la manière d'un tableur... Et non pas de manière relationnelle !

Commencez par remodéliser proprement votre base en respectant les formes normales, et les principes fondamentaux et tout ira bien, y compris si vous avez des millions de lignes dans votre table.
A me lire : https://blog.developpez.com/sqlpro/p...mances_petites

A +

[escartefigue]

Outre le problème de table obèse mentionné par SQLPro,
- vous utilisez une UNION, peut être qu'un UNION ALL suffirait et vous ferait gagner du temps
- vous utilisez des fonctions de colonnes dans le WHERE, ce qui compromet l'usage d'index même si toutefois il existait un index sur la colonne VALIDATION
- Il semble que la colonne RDT soit de format char ou varchar et qu'elle contienne des dates, c'est une énorme erreur, à corriger ou faire corriger si possible

Si vos 2 colonnes RDT (une fois corrigée) et VALIDATION sont de format date, vous pourrez demander la création d'un index sur ces colonnes, puis vous remplacerez votre filtre comme suit :

Code :

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

1
2
3
4
 
WHERE `validation` BETWEEN '2017-01-01' AND '2017-06-15'
  AND (    `RDT` BETWEEN '2017-04-01' AND '2017-04-30'
        OR `RDT` BETWEEN '2017-03-01' AND '2017-03-31')

[TryHarder]

Un peu brutale mais claire, merci pour cette réponse.

Les volumes n'avaient clairement pas été anticipé et il est vrai que jusque là la monotable simplifiait grandement la tâche.

Si je saisi correctement les choses, mieux vaut 120 voir 150 tables (pour des cas pas nécessairement extrêmes) qu'une seule ? Sachant que l'intégralité des données écritent dans une ligne (pour la monotable) se fait en une seule fois suite aux données d'un formulaire. Il faudrait donc déclencher 150 écritures puis faire des jointures sur ces 150 tables selon les besoins pour faire les choses correctement ?

Dans le cas de la scission de la monotable mieux vaut-il n'avoir au pire (ou au mieux ?) qu'une colonne (en plus de l'id commun aux tables), ou regrouper arbitrairement quelques colonnes - sachant qu'il y a des informations de base, fixes, mais le reste des colonnes n'est absolument pas connu à la création de la table puisque c'est l'utilisateur qui ajoute, modifie ou supprime des éléments, ce qui se répercute dans la structure de la table.

Il faut peut-être que je pose le contexte en quelques mots, si ça peut aider :

• Chaque utilisateur a sa propre table contenant quelques colonnes fixes, et une quantité indéterminée de colonnes (en moyenne 40 - mais parfois 150-200)
• C'est utilisateur qui ajoute ou supprime des éléments (ce qui modifie la structure de la base en conséquence)
• Des données sont collectées au fil de l'eau et enrichissent la table de l'utilisateur
• Sur la base des données de la table utilisateur sont établies des statistiques sur tout ou parti de celle-ci

Dans ce contexte, vous me confirmez que la meilleures des solutions est de splitter les bases utilisateurs en une multitude de petites bases ? Et donc que le gain de performance en lecture pour établir ces requêtes statistiques sera compenser par la perte nécessaire de temps en écriture sur de multiples tables ?

Encore merci.

[TryHarder]

Outre le problème de table obèse mentionné par SQLPro,
- vous utilisez une UNION, peut être qu'un UNION ALL suffirait et vous ferait gagner du temps
- vous utilisez des fonctions de colonnes dans le WHERE, ce qui compromet l'usage d'index même si toutefois il existait un index sur la colonne VALIDATION
- Il semble que la colonne RDT soit de format char ou varchar et qu'elle contienne des dates, c'est une énorme erreur, à corriger ou faire corriger si possible

Si vos 2 colonnes RDT (une fois corrigée) et VALIDATION sont de format date, vous pourrez demander la création d'un index sur ces colonnes, puis vous remplacerez votre filtre comme suit :

Code :

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

1
2
3
4
 
WHERE `validation` BETWEEN '2017-01-01' AND '2017-06-15'
  AND (    `RDT` BETWEEN '2017-04-01' AND '2017-04-30'
        OR `RDT` BETWEEN '2017-03-01' AND '2017-03-31')

Merci, ce sont des pistes temporaires avant un plus gros chantier. L'UNION ALL ne change rien cela a déjà été tester. VALIDATION et RDT sont tout deux des datetime et ont chacun un index. Malheureusement ça n'est pas si simple mais merci tout de même

[skuatamad]

VALIDATION et RDT sont tout deux des datetime et ont chacun un index

Un index par colonne ?
Et avec un index sur le couple (VALIDATION, RDT), qu'est ce que ça donne ?

[TryHarder]

Un index par colonne ?
Et avec un index sur le couple (VALIDATION, RDT), qu'est ce que ça donne ?

Intéressante question, je vais le tester. Mais c'est un faux problème puisque cette colonne n'est pas systématiquement dans la clause WHERE, je ne pouvais pas présenter l'exhaustivité des requêtes générées, juste un exemple simplifié.
Merci tout de même.

[SQLpro]

Un peu brutale mais claire, merci pour cette réponse.

Les volumes n'avaient clairement pas été anticipé et il est vrai que jusque là la monotable simplifiait grandement la tâche.

Si je saisi correctement les choses, mieux vaut 120 voir 150 tables (pour des cas pas nécessairement extrêmes) qu'une seule ? Sachant que l'intégralité des données écritent dans une ligne (pour la monotable) se fait en une seule fois suite aux données d'un formulaire. Il faudrait donc déclencher 150 écritures puis faire des jointures sur ces 150 tables selon les besoins pour faire les choses correctement ?

Cela s'appelle modéliser une base... C'est un métier ! Là vous avez transformé votre SGBD en tableur. Vous avez donc les performances d'un tableur au prix d'un SGBD !

Dans le cas de la scission de la monotable mieux vaut-il n'avoir au pire (ou au mieux ?) qu'une colonne (en plus de l'id commun aux tables), ou regrouper arbitrairement quelques colonnes - sachant qu'il y a des informations de base, fixes, mais le reste des colonnes n'est absolument pas connu à la création de la table puisque c'est l'utilisateur qui ajoute, modifie ou supprime des éléments, ce qui se répercute dans la structure de la table.

Encore une fois les principe de modélisation sont simple : si les n informations sont strictement toutes connues au même moment et toujours ensemble et toujours valide, alors oui. Un exemple : Entité PERSONNE, attribut nom, prenom, date de naissance, telephone, email => nom + prenom + date de naissance sont connues ensemble. Mais personne n'est obligé d'avoir un téléphone, ni un email => 3 entités : PERSONNE avec nom, prenom, date de naissance, TEKEPHONE avec numero et EMAIL avec compte_mail.

Il faut peut-être que je pose le contexte en quelques mots, si ça peut aider :

• Chaque utilisateur a sa propre table contenant quelques colonnes fixes, et une quantité indéterminée de colonnes (en moyenne 40 - mais parfois 150-200)
• C'est utilisateur qui ajoute ou supprime des éléments (ce qui modifie la structure de la base en conséquence)
• Des données sont collectées au fil de l'eau et enrichissent la table de l'utilisateur
• Sur la base des données de la table utilisateur sont établies des statistiques sur tout ou parti de celle-ci

Dans ce cas il aurait fallut mettre en oeuvre un méta modèle. Si vous aviez été sous un autre SGBD que MySQmerde, (par exemple sous MS SQL Server) je vous aurait suggéré de faire cela en XML en faisant croire par l'intermédiaire de vues de "mise à plat du XML" que ce sont des tables misajourable... A lire : https://blog.developpez.com/sqlpro/p...-modele-en-xml Mais sous mySQmerde le XML est impossible à mettre à jour et l'indexation XML n'existe pas....

Dans ce contexte, vous me confirmez que la meilleures des solutions est de splitter les bases utilisateurs en une multitude de petites bases ? Et donc que le gain de performance en lecture pour établir ces requêtes statistiques sera compenser par la perte nécessaire de temps en écriture sur de multiples tables ?

Encore merci.

Relisez l'article. SI vous avez 140 colonnes, pour optimiser tous les cas de figure de recherches dans vos 140 colonnes, il vous faudrait 87676763245694635005638098356456045 index... En supposant que MySQL accepte sur cette table autant d'index (!!!) vous allez passer 166812715459845195977241435229 ans à les poser à raison, d'un par minute sans prendre ni sommeil ni vacances... Est-ce bien raisonnable ?

A +

PS les américains disent "garbage in, garbage out" qui peut se résumer à : si tu as de la merde en entrée, tu aura de la merde en sortie !

[escartefigue]

VALIDATION et RDT sont tout deux des datetime et ont chacun un index.

En ce cas supprimez simplement la fonction "TIMESTAMP" pour rendre les index eligibles
Et si RDT est de type date, pourquoi communiquez vous en variable un format avec des slash ?

[TryHarder]

En ce cas supprimez simplement la fonction "TIMESTAMP" pour rendre les index eligibles
Et si RDT est de type date, pourquoi communiquez vous en variable un format avec des slash ?

Deux remarques pertinentes auxquelles je n'avais pas prêté attention, merci.

Pour le reste, je digère un peu. Évidement la modélisation d'une base est un métier, et évidement ça n'est pas le mien et c'est bien pourquoi j'essaye de combler des lacunes sans prétendre égaler un expert en la matière.

[escartefigue]

Si vous avez la possibilité de modifier la base de données comme vous le souhaitez, mais que seule la compétence vous manque, il y a des ouvrages gratuits disponibles sur le net, vous pouvez commencer par "parlez vous merise" de Michel Diviné, ouvrage d'une lecture facile et compréhensible par tous. Il y a également sur ce forum des chapitres entiers sur ce sujet. Notamment ici : https://www.developpez.net/forums/f6...sation/schema/

[Artemus24]

Salut tryharder.

je ne pouvais pas présenter l'exhaustivité des requêtes générées, juste un exemple simplifié.

Erreur classique de celui qui cherche à améliorer les performances d'une requête en donnant un exemple simplifié.
On ne peut pas améliorer les performances d'une requête si l'on n'a pas une vue d'ensemble.

Le conseil avisé de SQLPRO est de commencer par modéliser correctement votre base de données.

Dans ce cas il aurait fallut mettre en oeuvre un méta modèle.

Excellente remarque, sauf que MySql ne sait pas faire cela et je suppose que sa mise en oeuvre n'est pas à la portée de TryHarder.

Code :

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

1
2
3
ROUND(AVG(IF(`R2`!="0",R2,NULL)),1) AS "R2",
SUM(IF(`R2`!="0",1,0)) AS "R2E[]",
STD(NULLIF(`R2` ,0)) AS "R2D1",

Pourquoi répétez-vous sur la colonne 'R2' la même mise en forme "IF(`R2`!="0",R2,NULL)"?
Ca alourdit votre requête, cela fait perdre du temps car la même mise en forme va se répéter à chaque demande.
Créez plutôt une sous-requête qui va réaliser 1 fois cette mise en forme. Et dans la requête principale exploiter cette colonne.

@+

[TryHarder]

Erreur classique de celui qui cherche à améliorer les performances d'une requête en donnant un exemple simplifié.
On ne peut pas améliorer les performances d'une requête si l'on n'a pas une vue d'ensemble.

Le reste de la requête est vraiment la copie conforme de la séquence mais répéter sur n colonnes. Mais soit, j'aurais effectivement pu.

Excellente remarque, sauf que MySql ne sait pas faire cela et je suppose que sa mise en oeuvre n'est pas à la portée de TryHarder.

Je suis actuellement un peu limité par la technologie, mySQL, sqlite, posteOgre. Mais l'idée d'un méta modèle n'est pas fermée sur du moyen/long terme. Je dois m'informer et me former sur ce principe et les moyens de le mettre en œuvre.

Pourquoi répétez-vous sur la colonne 'R2' la même mise en forme "IF(`R2`!="0",R2,NULL)"?
Ca alourdit votre requête, cela fait perdre du temps car la même mise en forme va se répéter à chaque demande.
Créez plutôt une sous-requête qui va réaliser 1 fois cette mise en forme. Et dans la requête principale exploiter cette colonne.

Pour le court terme, j'aimerais bien optimiser cette requête mais là je ne suis pas certain de la mise en pratique de cette remarque qui parait judicieuse.

Je me trompe peut-être mais le

Code :

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

IF(`R2`!="0",R2,NULL)

semble bien redondant avec le

Code :

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

NULLIF(`R2` ,0)

mais pas le

Code :

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

IF(`R2`!="0",1,0)

?

Concernant la mise en pratique j'ai bien effectué quelques essais dont

Code :

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

1
2
3
4
5
6
7
 
SELECT
(SELECT NULLIF(`R2` ,0) FROM `tbl`) AS "R2SUB",
ROUND(AVG("R2SUB"),1) AS "R2",
SUM(IF(`R2`!="0",1,0)) AS "R2E[]",
STD("R2SUB") AS "R2D1"
FROM `tbl` WHERE 1

qui ne marche évidement pas, et quelques autres tentatives toutes aussi infructueuses. Si vous pouviez éclairer ma lanterne...

En tous cas, merci pour vos retours.

PS : J'ai essayer de mettre ma requête à titre informatif dans le post, mais je dépasse la limite de caractères