IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Requêtes MySQL Discussion :

optimization table 500mb - solutions ?


Sujet :

Requêtes MySQL

  1. #1
    Nouveau membre du Club
    Inscrit en
    Juillet 2003
    Messages
    30
    Détails du profil
    Informations forums :
    Inscription : Juillet 2003
    Messages : 30
    Points : 26
    Points
    26
    Par défaut 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

    a+

  2. #2
    Modérateur

    Avatar de CinePhil
    Homme Profil pro
    Ingénieur d'études en informatique
    Inscrit en
    Août 2006
    Messages
    16 793
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 60
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur d'études en informatique
    Secteur : Enseignement

    Informations forums :
    Inscription : Août 2006
    Messages : 16 793
    Points : 34 024
    Points
    34 024
    Billets dans le blog
    14
    Par défaut
    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.

    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 ?

    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.

    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.
    Philippe Leménager. Ingénieur d'étude à l'École Nationale Supérieure de Formation de l'Enseignement Agricole. Autoentrepreneur.
    Mon ancien blog sur la conception des BDD, le langage SQL, le PHP... et mon nouveau blog sur les mêmes sujets.
    « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
    À la maison comme au bureau, j'utilise la suite Linux Mageïa !

  3. #3
    Nouveau membre du Club
    Inscrit en
    Juillet 2003
    Messages
    30
    Détails du profil
    Informations forums :
    Inscription : Juillet 2003
    Messages : 30
    Points : 26
    Points
    26
    Par défaut 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 PHP : Sélectionner tout - Visualiser dans une fenêtre à part
    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

  4. #4
    Modérateur

    Avatar de CinePhil
    Homme Profil pro
    Ingénieur d'études en informatique
    Inscrit en
    Août 2006
    Messages
    16 793
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 60
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur d'études en informatique
    Secteur : Enseignement

    Informations forums :
    Inscription : Août 2006
    Messages : 16 793
    Points : 34 024
    Points
    34 024
    Billets dans le blog
    14
    Par défaut
    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é ! )
    Philippe Leménager. Ingénieur d'étude à l'École Nationale Supérieure de Formation de l'Enseignement Agricole. Autoentrepreneur.
    Mon ancien blog sur la conception des BDD, le langage SQL, le PHP... et mon nouveau blog sur les mêmes sujets.
    « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
    À la maison comme au bureau, j'utilise la suite Linux Mageïa !

  5. #5
    Nouveau membre du Club
    Inscrit en
    Juillet 2003
    Messages
    30
    Détails du profil
    Informations forums :
    Inscription : Juillet 2003
    Messages : 30
    Points : 26
    Points
    26
    Par défaut
    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

  6. #6
    Modérateur

    Avatar de CinePhil
    Homme Profil pro
    Ingénieur d'études en informatique
    Inscrit en
    Août 2006
    Messages
    16 793
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 60
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur d'études en informatique
    Secteur : Enseignement

    Informations forums :
    Inscription : Août 2006
    Messages : 16 793
    Points : 34 024
    Points
    34 024
    Billets dans le blog
    14
    Par défaut
    Citation Envoyé par MatthieuFourcade Voir le message
    #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 !

    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 !
    Philippe Leménager. Ingénieur d'étude à l'École Nationale Supérieure de Formation de l'Enseignement Agricole. Autoentrepreneur.
    Mon ancien blog sur la conception des BDD, le langage SQL, le PHP... et mon nouveau blog sur les mêmes sujets.
    « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
    À la maison comme au bureau, j'utilise la suite Linux Mageïa !

  7. #7
    Nouveau membre du Club
    Inscrit en
    Juillet 2003
    Messages
    30
    Détails du profil
    Informations forums :
    Inscription : Juillet 2003
    Messages : 30
    Points : 26
    Points
    26
    Par défaut
    Vous avez bien raison. Je vais pousser dans ce sens . Merci de votre aide !

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Requete sur 3 tables. Quelle solutions?
    Par youki_22 dans le forum Requêtes
    Réponses: 1
    Dernier message: 16/07/2008, 13h27
  2. qelle est la meilleure solution pour mes tables
    Par une_tite_question dans le forum Débuter
    Réponses: 2
    Dernier message: 30/06/2008, 19h56
  3. [MySQL] solution pour modifier la mise a jour d'une table
    Par mayers dans le forum PHP & Base de données
    Réponses: 2
    Dernier message: 27/07/2007, 23h32
  4. Optimize table sur table utilisée: LOCK ? COMMIT ?
    Par psychomatt dans le forum Outils
    Réponses: 3
    Dernier message: 25/01/2006, 18h45
  5. Optimized table
    Par rexxys dans le forum SQL Procédural
    Réponses: 1
    Dernier message: 18/10/2005, 14h01

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo