Discussion :

[euridice]

bonjour, je suis complètement débutant et me pose quelques questions relatives à la performance d'un site en local que je tente de développer.

J'ai une table unique de 86000 lignes, environ 250 colonnes, qui est une liste d'objets, indexés par un champ id
les autres colonnes représentent des caractéristiques de ces objets, par exemple name, classes, itemtype, slots, reqlevel, taille, couleur, grosseur, etc etc...

mon code php boucle sur un array appelé $slot qui contient 17 valeurs, puis sur un array $class qui en contient 15 donc 255 requêtes suivantes :

Code :

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

1
2
3
4
5
foreach ($slot as $key=>$value) {
foreach ($level as $keyl=>$valuel) {
$sqltest='SELECT `id`,`name`,IF(`reclevel`<'.$keyl.','.$fieldsort_select.',ROUND('.$fieldsort_select.'*'.$keyl.' / `reclevel`)) AS displayed FROM `items` WHERE `itemtype` <> 54 AND `classes` & '.$class_select.' AND `slots` & '.$key.' AND `reqlevel`<='.$valuel.' AND `reclevel`<='.$keyl.' AND `races`<>0  AND `races`<>65536 AND `notransfer`=0 ORDER BY `displayed` DESC LIMIT 10';
}
}

et en gros ca me prend 255*1/2s pour effectuer ces boucles ce que je trouve trop long.

les choses que j'ai remarqué après avoir lu les diverses FAQ et pages concernant l'optimisation sont les suivantes :

a) ORDER BY effectivement plombe les performances, si je l'enlève de la requête j'arrive effectivement à des bien meilleurs résultats, le problème devient alors : est-il plus rapide de classer mes résultats en PHP que ce que mysql va faire, j'espère ne pas m'emmêler les pinceaux mais si je supprime ce ORDER BY je vais devoir changer la structure de mon code en php.

b) j'ai testé en indexant le paramètre sur lequel ORDER BY est effectué, cela améliore sensiblement le temps de chaque requête c'est appréciable, le problème dans mon cas est double :
-d'abord je ne sais pas d'avance sur quel champ je vais effectuer cette requête et donc potentiellement si je devrais indexer tous les champs de ma table, n'ayant pas d'expérience en programmation et base de donnée ca me paraît extrême mais bon pourquoi pas. autre possibilité peut etre est d'indexé le champ quand la requête se lance, je ne sais pas si cela est très propre.

-et deuxièmement, ce ORDER BY peut être effectué sur une combinaison linéaire de différents champs ce qui a mon avis rend l'indexation de tous les champs inutile au moins dans ce cas. pour donner un petit exemple, une requête simple serait effectué avec un $fieldsort_select qui vaut 'mana':

Code :

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

SELECT `id` , `name` , IF( `reclevel` <5, `mana` , ROUND(`mana`*5 / `reclevel` ) ) AS displayed FROM `items` WHERE `itemtype` <>54 AND `classes` &2 AND `slots` &4 AND `reqlevel` <=5 AND `reclevel` <=5 AND `races` <>0 AND `races` <>65536 AND `notransfer` =0 ORDER BY `displayed` DESC LIMIT 10

et par exemple sur un $fieldsort_select qui est égal à 'mana*5+hp*10':

Code :

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

SELECT `id` , `name` , IF( `reclevel` <5, ('mana*5+hp*10) , ROUND(('mana*5+hp*10)*5 / `reclevel` ) ) AS displayed FROM `items` WHERE `itemtype` <>54 AND `classes` &2 AND `slots` &4 AND `reqlevel` <=5 AND `reclevel` <=5 AND `races` <>0 AND `races` <>65536 AND `notransfer` =0 ORDER BY `displayed` DESC LIMIT 10

c) le mot clé explain devant ma requête ne m'apporte pas grand chose, du moins je n'en tire pas grand chose à mon niveau.
voilà pour une requête particulière de la boucle :

Code :

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

1
2
3
4
 
EXPLAIN SELECT `id` , `name` , IF( `reclevel` <5, `mana` , ROUND(`mana`*5 / `reclevel` ) ) AS displayed FROM `items` WHERE `itemtype` <>54 AND `classes` &2 AND `slots` &4 AND `reqlevel` <=5 AND `reclevel` <=5 AND `races` <>0 AND `races` <>65536 AND `notransfer` =0 ORDER BY `displayed` DESC LIMIT 10
id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	items 	ALL 	NULL 	NULL 	NULL 	NULL 	86374 	Using where; Using filesor

d) analyze table ne me dit rien

voilà j'espère avoir décrit de manière clair mes interrogations et merci d'avance de votre lecture.

[CinePhil]

Le problème se situe peut-être là :

J'ai une table unique de 86000 lignes, environ 250 colonnes,

Une seule table dans une BDD, c'est utiliser un SGBD comme un tableur ; autant utiliser un tableur !

qui est une liste d'objets, indexés par un champ id
les autres colonnes représentent des caractéristiques de ces objets, par exemple name, classes, itemtype, slots, reqlevel, taille, couleur, grosseur, etc etc...

Je suppose que tous les objets n'ont pas toutes les caractéristiques renseignées ?

Tu as donc, dans cette hypothèse, des objets qui ont certaines caractéristiques.

Règle de gestion :
Un objet peut avoir plusieurs caractéristiques et une caractéristique peut qualifier plusieurs objets.

MCD :
Caracteristique -0,n----Qualifier----0,n- Objet

Tables qui en découlent :
Caracteristique (car_id, car_libelle)
Objet (obj_id, obj_nom...)
Objet_caracteristique (ocr_id_objet, ocr_id_caracteristique, ocr_valeur)

Les clés primaires sont soulignées et les clés étrangères sont en italique.

Seront indexés :
- car_id (clé primaire)
- obj_id (clé primaire)
- le couple {ocr_id_objet, ocr_id_caracteristique} (clé primaire)

Tu poseras un index sur :
- car_libelle
- obj_nom
- ocr_id_caracteristique qui n'est pas indexée individuellement par la clé primaire

Il faudra bien sûr revoir tes requêtes en faisant des jointures entre les tables quand c'est nécessaire.

Bon courage !

[euridice]

Je suppose que tous les objets n'ont pas toutes les caractéristiques renseignées ?

c'est effectivement le cas
je crois avoir compris les changements de structure que tu préconises, mais pour essayer de gagner un peu de temps, est-il exact de penser que je devrais commencer par les colonnes de ma tables les moins renseignées ?

après il va falloir que je code qqch qui automatise la chose car cela peut devenir vite très pénible à la main, mais peut être cela existe-il ?

[SQLpro]

Lorsque je fais des audits de performance (pas sur MySQL, car performance et MySQL c'est relativement antinomique...) sachez qu'à partir de plus de 20 colonnes dans une table, je sabre !

A +

[euridice]

re bonjour,
après avoir effectué les modifications sur ma table suivant les conseils de CinePhil, je bloque sur une question de logique je pense

j'ai tenté de relire les papier de sqlpro sur les jointures, mais la je colle sur mes nouvelles requêtes !

pour résumer, j'ai 3 tables désormais
a) carac (id, name)
b) items (id, name, .....)
c) itemscarac (id_items, id_carac, value)

items.id est liée à itemscarac.id_items elle-même liée à carac.id

maintenant si je veux filtrer sur un couple carac.id / itemscarac.value je fais comment ?

je ne comprends pas bien comment faire ca :

- le couple {ocr_id_objet, ocr_id_caracteristique} (clé primaire)

qui correspond chez moi à id_items, id_carac

je voudrais pas exemple les id et name de la table items pour des valeur de (id_carac=3 et itemscarac.value <> 54) d'une part et (id_carac=4 et itemscarac.value & 128) d'autre part

la seule solution que j'ai trouvé est la suivante, c'est toujours lent et pas joli j'imagine.

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
SELECT items.id, items.name, itemscarac. * , carac. *
FROM items
JOIN itemscarac ON items.id = itemscarac.id_items
JOIN carac ON itemscarac.id_carac = carac.id
WHERE (
id_carac =3
AND itemscarac.value <>54
)
UNION 
SELECT items.id, items.name, itemscarac. * , carac. *
FROM items
JOIN itemscarac ON items.id = itemscarac.id_items
JOIN carac ON itemscarac.id_carac = carac.id
WHERE (
id_carac =4
AND itemscarac.value &128
)

je pense rater quelque chose d'essentiel dans les jointures mais la je sèche

merci d'avance de voter patience