Optimisation de base de donnée MySQL

Version imprimable

30/01/2007, 17h10
gnafron

Optimisation de base de donnée MySQL

Bonjour,

J'ai developpé une application en PHP/MySQL pour le boulot il y a à peu près deux ans et beaucoup de choses sont venus s'ajouter depuis.

Bref, je souhaiterai repenser ma base pour l'optimiser mais la structure est un peu complexe.
Pour faire simple il s'agit d'un tableau avec un nombre de colonnes et de lignes dynamiques.

Les lignes viennent d'une autre base, donc vraiment dynamiques.
Le nombre de colonnes et leur nom est paramétré par l'administrateur.

En partant de ces hypotheses, j'ai donc créé une table qui en gros ressemble à ca :

id tableau / n° colonne / n° ligne / valeur

sauf que pour un tableau de 5 sur 5 j'ajoute 25 lignes et que j'en suis à 200 000 lignes au total dans cette table.

Comme il y a pas mal de stats qui tourne dessus, ca commence à ramer serieusement.

Y a t'il une technique pour arranger cela un peux mieux et eviter d'avoir une base enorme, très gourmande lorsque qu'on fait des stats dessus ?

J'espere que c'est clair ^^
Merci d'avance
31/01/2007, 12h20
Adjanakis

Bonjour,

Quelle est le script SQL de création de cette table ? Est-il possible d'avoir un exemple de requête prenant trop de temps ?
01/02/2007, 08h04
gnafron

Non je prefere pas mais je peux te donner l'allure des tables et de la requete

Table resultats (1 ligne par case du tableau) :

id (clé primaire, auto increment)
codetableau
date
utilisateur
noligne
nocolonne
valeursaisie

Table lignes (1 ligne par ligne du tableau)

id (clé primaire, auto increment)
codetableau
date
utilisateur
noligne
parametre1
parametre2

Requete :

But calculer la somme de parametre1xparametre2 pour les lignes du tableau ou il y a valeursaisie=X (X connu et fixe) sur une periode (deux dates)

Pour cela j'utilise PHP. Je parcoure dans une boucle toutes les lignes de la table resultats ou valeursaisie=X
Et dans la boucle je vais chercher les valeurs de parametre1 et parametre2 pour faire mon operation, donc jointure entre les deux tables, ce qui plombe le temps de requete.

Je sais que c'est difficile comme ca, mais la question est plus comment tu ferais que comment j'ai fais. Une idée ?
01/02/2007, 11h12
Sivrît

Citation:

Envoyé par gnafron

Pour cela j'utilise PHP. Je parcoure dans une boucle toutes les lignes de la table resultats ou valeursaisie=X
Et dans la boucle je vais chercher les valeurs de parametre1 et parametre2 pour faire mon operation, donc jointure entre les deux tables, ce qui plombe le temps de requete.

Ceci est le mâââl ! 8O

Il vaudrait vraiment mieux faire ça avec une jointure. Ca donne une seule requête avec un 'JOIN' qui donne directement le résultat, il ne devrait pas y avoir comparaison.

La demande du script de création était aussi là pour voir les indexes définis. Arrivé à ces tailles de tables les indexes deviennent vraiment vitaux. Ils prennent de la place et les maintenir à jour a un coût à équilibrer avec ce qu'ils apportent... mais au moins les champs comme 'noligne' semblent incontournables à indexer.
01/02/2007, 22h08
gnafron

Il me semblait bien ...

J'ai jamais utilisé de JOIN (inner, outer, etc ... jamais rien compris), si c'est bien de ca que tu parles ou puis je trouver un tuto très très simpliste pour commencer à ce sujet ?

Et pour confirmer les champs id sont des clés primaires mais tous les autre sont des champs normaux. Par contre un index ne doit il pas etre unique ? (hors noligne est loin d'etre unique)
02/02/2007, 11h15
Sivrît
Citation:

Envoyé par gnafron

J'ai jamais utilisé de JOIN (inner, outer, etc ... jamais rien compris), si c'est bien de ca que tu parles ou puis je trouver un tuto très très simpliste pour commencer à ce sujet ?

http://sql.developpez.com/ ou alors :google:

Dans l'idée ce sera du :
Code:

1 2 3 4 SELECT ... FROM resultat INNER JOIN ligne ON resultat.noligne=ligne.noligne AND resultat.codetableau=ligne.codetableau WHERE ...
Pour en revenir à cette histoire de somme ce sera un poil plus délicat. Avec un mysql assez récent il va falloir une sous-requête (genre avec 'EXISTS' pour faire joli).

Citation:

Envoyé par gnafron

Et pour confirmer les champs id sont des clés primaires mais tous les autre sont des champs normaux. Par contre un index ne doit il pas etre unique ? (hors noligne est loin d'etre unique)

Pas nécessairement. Ils peuvent même servir à retrouver des tranches (par exemple les date entre t1 et t2).

Concernant la structure de la table, est-ce que les champs 'codetableau', 'date' et 'utilisateur' de la table 'resultat' ne font pas double emploi avec ceux de la table 'ligne' ? En outre il serait peut être préférable que 'resultat.noligne' devienne 'resultat.ligne' et référence 'ligne.id'.