optimization table 500mb - solutions ?

Version imprimable

04/11/2009, 15h47
MatthieuFourcade

optimization table 500mb - solutions ?

Bonjour a tous,

Je me decide a poster une petite question pour etre sur et dans la mesure ou je n'ai pas reussi a trouver vraiment une reponse...

Voici le probleme:

J'ai une table comportant 2.5 Millions de lignes et 10 colonnes.

J'ai des requetes du types SELECT * FROM Table Where XXX = YYY Group By ZZZ Order By NNN

J'ai bien evidement quelques problemes de perf. Pour eviter ca pour l'instant j'ai mis des clauses du type Colonne = "valeur" AND Colonne2 = "valeur2" ...

Mais dans l'absolut, et pour avoir une meilleur "pertinence" je souhaite utiliser au mieux le FullText sur 2/3 colonne ou un Like: Colonne = "valeur%" AND ...

Sur la colonne deux pas de probleme pour le fulltext ou like. Mais sur la colonne 1 qui est du type "phrase" autant dire qu'on fleurte sur du 10 000 - 50 000 resultats et ca ne devient plus possible

Je sais bien que ca n'a pas d'interet outre mesure d'avoir 50000 resultats. Mais ne pas pouvoir faire un full text sur 3 colonnes en gardant les resultats les plus pertinents m'oblige a orienter les visiteurs de facon plus restrictives.

Donc ma question: Comment optimiser tout ca sans parler des index et requetes ???

#1: Changer de moteur ? je me posais la question pour le InnoDB ou le MEMORY engine sachant que ma base fait 500 ~Mo

#2: Demander une autre solution a mon hebergeur ?

Est-il possible d'avoir des serveurs dedié et tournant en parallele ? dans quel ordre de prix ? 500-1000 euros / an ? voir plus ?

Merci par avance pour vos conseils :D

a+
04/11/2009, 16h11
CinePhil

On peut avoir la structure de la table, un exemple de requête réelle et un petit jeu de données exemple pour qu'on comprenne le problème ?
Parce que 2,5 millions de lignes, ce n'est pas forcément un gros problème, j'ai connu beaucoup plus : une table de 12 colonnes contenant 65 millions de lignes.
Et des jointures avec d'autres tables de plusieurs millions ou dizaines de millions de lignes elles aussi.

Quelques conseils :
1) Evitez au maximum le SELECT * quand vous n'avez pas besoin de toutes les colonnes.
2) Placez les index pertinents sur votre table, le gain en temps est spectaculaire.
3) GROUP BY et ORDER BY, tout comme DISTINCT, sont très gourmands en ressources. Vérifiez si c'est toujours indispensable.

Citation:

Pour eviter ca pour l'instant j'ai mis des clauses du type Colonne = "valeur" AND Colonne2 = "valeur2" ...

Je suppose que c'est ce qu'il y a dans le WHERE ?
En quoi ça change quelque chose par rapport à ce qu'il y avait avant ?

Citation:

Mais dans l'absolut, et pour avoir une meilleur "pertinence" je souhaite utiliser au mieux le FullText sur 2/3 colonne ou un Like: Colonne = "valeur%" AND ...

Il faut voir le contexte mais une recherche FULLTEXT ne sera pas forcément plus rapide, bien au contraire car pas d'utilisation d'index possible !

Idem pour le LIKE qui abolit l'utilisation de l'index.

Citation:

Sur la colonne deux pas de probleme pour le fulltext ou like. Mais sur la colonne 1 qui est du type "phrase" autant dire qu'on fleurte sur du 10 000 - 50 000 resultats et ca ne devient plus possible

Là encore, un exemple concrêt nous aiderait à vous aider.

Un exemple :D

C'est que cela sera plus simple avec un exemple:

TABLE:

ID - 1
Poste - Ingenieur de developpement Java
Service - Informatique
Date1 - 20011211
Date2 - 00000000
Bureau - Paris
Departement - 75
Coefficient - 1.2
Superieur - Dupont

Requete:
Code:

1 2 3 4 5 6 7 8 9 $select = "SELECT DISTINCT Poste, Service, Date1, Date2, Coefficient, Bureau, Departement, Superieur, MATCH (Poste, Service, Bureau) AGAINST (\"$search\"), MATCH (Poste, Service, Bureau) AGAINST (\"$search\" IN BOOLEAN MODE) FROM Data WHERE MATCH (Poste, Service, Bureau) AGAINST (\"$search\" IN BOOLEAN MODE) >= 1 ORDER BY MATCH (Poste, Service, Bureau) AGAINST (\"$search\" IN BOOLEAN MODE) DESC, MATCH (Poste, Service, Bureau) AGAINST (\"$search\") DESC LIMIT 0,100"
Voila en esperant que ca aide ;)

04/11/2009, 16h46
CinePhil

Quelques questions :
1) $search peut contenir quoi ?
2) A quoi est censée servir cette requête ?
3) Le modèle de données peut-il être modifié ? (il n'est pas du tout optimisé ! :aie: )
04/11/2009, 16h50
MatthieuFourcade

Salut,

Merci de cette reponse rapide:

#1: $search est du style: "ingenieur* informatique* paris*"

J'ajoute les * pour tenir compte au max du FullText et ainsi eviter les erreurs de frappes et retourner tout les resultats semblables. Ainsi "info* inge* pari*" donnera les memes resultats ~~~

#2: Cette requete est pour rechercher un employé et retourner ses attibuts, position, hierarchie ...

#3: ???

Merci
04/11/2009, 17h28
CinePhil

Citation:

Envoyé par MatthieuFourcade

#3: ???

Je vois dans cet exemple de données que les colonnes Poste, Service, Bureau et Superieur sont de type texte.
En gros cette table est un immense tableau qui ne peut pas contenir dans un tableur, ce n'est pas une base de données relationnelle ! :aie:

Nomalement, vous devriez avoir les relations suivantes :
1) Une personne est affectée à un service et un service peut accueillir plusieurs personnes.

MCD :
Personne -1,1----Affecter----0,n- Service

Si les données enregistrent l'historique de l'affectation des personnes à différents services, on a alors la règle de gestion :
"Une personne est affectée à un service durant une période et un service peut accueillir plusieurs personnes"

Et le MCD devient
Personne -1,n----Affecter (période)----0,n- Service

La période est une donnée portée par la relation et entre en jeu dans l'identification.

2) Un service est situé dans un bureau et un bureau a plusieurs services.

MCD :
Service -1,1----Situer----0,n- Bureau

3) Une personne peut avoir un supérieur et un supérieur encadre une a plusieurs personnes. Le supérieur étant lui même une personne.

MCD :
Personne -1,n----Encadrer----0,1- Personne

J'arrête là c'est déjà pas mal.

Ca donnerait les tables suivantes :
Bureau (B_Id, B_Nom...)
Service (S_Id, S_Nom, S_IdBureau...)
Personne (P_Id, P_Nom, P_Prenom,...)
Affectation (A_IdPersonne, A_IdService, A_DateDebut, A_DateFin...)

Ainsi vous auriez une base de données structurée au lieu d'une seule table qui contient tout et dans laquelle il peut y avoir des erreurs ou des similaires pas forcéments pistables par la requête (inforamtique au lieu de informatique, Saint-Etienne et Saint Etienne...).

Et je ne sais pas comment est fait votre logiciel mais si je cherche le personnel ayant été affecté au bureau de Paris, je trouverais plus pratique d'avoir une liste qui me propose la liste de tous les bureaux. Si je cherche un ingénieur en informatique, une liste avec toutes les fonctions (tiens ! on en n'a pas parlé dans la modélisation de celle là !) conviendrait mieux que l'angoisse de la zone de texte blanche. Et si plusieurs critères doivent être pris en compte, et bien il y a plusieurs listes ou zones pour chaque concept différent (poste, service, bureau...).

La recherche FULLTEXT est selon moi plus appropriée à la recherche de mots ou de phrases dans des textes et nom pas dans des libellés courts qui figurent normalement dans des colonnes de type VARCHAR.

Je vous laisse réfléchir à tout ça. Peut-être n'y pouvez-vous rien mais pas étonnant qu'avec 2,5 millions de lignes et des MATCH dans tous les sens, la requête soit longue !

Bon courage !
04/11/2009, 17h35
MatthieuFourcade

Vous avez bien raison. Je vais pousser dans ce sens . Merci de votre aide !