Forum Programmation.autre table SQL à optimiser

Posté par  (site web personnel) . Licence CC By‑SA.
Étiquettes :
0
14
avr.
2014

Je connais très peu les bases de données et j'aimerais savoir comment optimiser une table pour un projet Python3/sqlite3. Voici les contraintes :

  • 5 champs dont deux sur lesquels portent les recherches dans la base : "form"(text) et "searchfreq"(integer)
  • je n'ai pour le moment pas créé d'index unique
  • la base, une fois créée, s'enrichit à l'aide d'INSERT successifs; aucune autre opération n'est effectuée en écriture sur la base.
  • une fois la base créée, des recherches portant sur les champs "form" et "searchfreq" sont possibles. Une recherche typique permet de chercher la/les ligne(s) correspondant à un "form" donné; une autre recherche possible est de récupérer les lignes ayant le "searchfreq" le plus élevé.
  • la base de données contiendra quelques centaines de milliers de lignes.

Pour le moment, j'ai très simplement créé la table de la manière suivante :

sql_order = "CREATE TABLE forms " \
     "(form TEXT, " \
     "searchfreq INTEGER, " \
     "sourceword TEXT, " \
     "inflection_id INTEGER, " \
     "context TEXT)"

Tout ceci fonctionne bien mais j'aimerais savoir comment accélérer les recherches sur "form" et sur "searchfreq" : pour le moment, la base de données est encore petite et j'aimerais anticiper la suite.

Merci d'avance !

  • # Re:

    Posté par  . Évalué à 2.

    Salut,

    "Anticiper la suite" c'est d'abord mettre une clé primaire sur ta table. Il faut que tu puisses identifier une ligne facilement sans te baser sur "form" qui plus est est au format TEXT.

    Pour les index c'est simple: un sur form, un sur searchfreq.

    Après, les INTEGER c'est très bien, par contre les TEXT ca l'est moins. Si tu peux éviter, évite, ou remplace les au moins par des VARCHAR si leur contenu ne dépasse pas qqs centaines de caractères.

    Je ne sais pas ce que ta table va contenir, mais par exemple, si la colonne context contient souvent la même chose, crée plutôt une table contexts (context_id INTEGER, context VARCHAR) que tu pourras référencer dans ta table forms.

    • [^] # Re:

      Posté par  (site web personnel) . Évalué à 1.

      apkwa : Merci pour tous ces renseignements. J'ai pu ajouter toutes les modifications recommandées sauf le "context_id" car "context" est rempli d'informations non redondantes. Je publierai bientôt sur LinuxFr un journal faisant référence au coup de pouce que tu viens de me donner.

      Trust the Python !

      • [^] # Re:

        Posté par  . Évalué à 2.

        De rien.
        Je viens de voir ton journal, et le projet est intéressant. Bon courage!

    • [^] # Re:

      Posté par  . Évalué à 2.

      j'ai une question un peu idiote…

      un index sur un champ qui va être unique ? La probabilité qu'il y ait pas 2 champs form identique est faible, ainsi il me semble que le parcours de l'index sera aussi long que le parcours de la table, rendant l'opération pas tellement intéressante, sauf à augmenter (doubler ?) la place sur le disque ?

      • [^] # Re:

        Posté par  . Évalué à 1.

        Salut,

        Ben en fait, dans le post original, il est indiqué que pour l'instant, il n'y a aucune clé unique.
        Ceci dit, si tu ajoutes une contrainte d'unicité sur ta colonne, en fait tu n'as pas le choix: un index est créé implicitement.
        Si ce n'était pas le cas, ca vaudrait tout de même le coup d'en créer un car il est ordonné, ce qui n'est pas le cas de ta table. Donc la recherche est tout de même (beaucoup) plus rapide avec un index, même si tu as des valeurs uniques dans tous tes champs (tu évites le full scan).

        • [^] # Re:

          Posté par  . Évalué à 2.

          je ne parlais QUE pour le champ text qui va se retrouver avec le contenu du champ form. Dans mon esprit pervers, c'est un champ libre ou on peut taper ce que l'on veut, y mettre un index, c'est … intéressant, à étudier.

          J'avais il y a une pelle d'année mis des index full search sur des champs textes dans le seul but de faire une recherche par pertinence et je n'ai été que moyennement convaincu, parce que je n'avais pas assez d'enregistrement moins de 1000 avec peu de proximités et plein de mots équivalent et des textes long pour que cela change vraiment la donne : par exemple avoir entre 1.5 et 4.5 % de pertinence sur une recherche ça affole pas les foules, même si avec très peu de travail ca claque sa mère pour un lambda (ce que sont les utilisateurs).

          A faire des tests (mais là encore pas assez d'enregistrements pour faire un vrai test et dupliquer ne sert à rien car on sort du cadre) je ne voyais pas une grosse différence avec une recherche like %tructruc% or like %tructruc% … à part la simplicité de ne pas se faire chier à gérer ses requêtes multis mots et à part le fait de ne plus pouvoir faire des +mots -mots pour choisir d'avoir ou pas certains mots.

          Je ne pense pas que le tri alphabétique des enregistrements soit un phénomène pertinent puisqu'il m'avait semblé comprendre que le concept de bdd c'est que chaque enregistrement (la feuille) est à la même distance du tronc que n'importe quel autre et donc que chercher le mot werrtd va aussi vite que chercher azert et les rentrer dans l'ordre ne change pas grand chose.

          Enfin des bases comme mysql sont/étaient(?) pauvres en fonctionnalités par choix de pouvoir traiter rapidement de grosses masse de données, contrairement à certaines autres (oracle,posgresql..) qui pour offrir plus de fonctionnalités avait des temps de recherche moins performant hors tweekenning complexe à mettre en oeuvre et adapté au cas par cas à la suite de tests et tests en tout sens.

          Mais je veux bien envisager que je me fourvoie, maintenant le coup de l'ordre alphabétique et du tri, ca fait tomber un peu tout un pan de ce que je croyais savoir.

          • [^] # Re:

            Posté par  . Évalué à 3.

            Tout d'abord, attention sur les index sur des colonnes TEXT, ce n'est pas supporté par tout le monde. Je ne sais pas pour sqlite, mais MySQL ne le permet qu'avec un moteur MyISAM.

            je ne voyais pas une grosse différence avec une recherche like %tructruc% or like %tructruc%

            C'est normal, car dans ce cas, tu n'utilises pas l'index. L'index créé implicitement n'est utilisé que si tu as le début des valeurs contenues dans tes colonnes (càd pas de %pattern% mais des pattern%). Dans ce cas, il faut créer un index full-text, et utiliser les fonctions qui vont avec (cf MATCH pour du MySQL) mais la encore, je renvoie au début de ma réponse: tout le monde ne supporte pas forcément le full-text search.

            puisqu'il m'avait semblé comprendre que le concept de bdd c'est que chaque enregistrement (la feuille) est à la même distance du tronc que n'importe quel autre et donc que chercher le mot werrtd va aussi vite que chercher azert et les rentrer dans l'ordre ne change pas grand chose.

            Oui, tout est "presque à la même distance" si tu utilises des index. Ce n'est pas du tout vrai sans index.
            Certains SGBD permettent d'ordonner ta table, de telle sorte que les nouvelles lignes s'insèrent au bon endroit (pas toujours au tout bas de ta table). Dans ce cas, effectivement, ta table te sert d'index directement, mais ce n'est pas fait sans action de ta part (cf clustered index, avec pas mal de limitations en MySQL).

            En fait, je crois que tu penses que quand tu insères une nouvelle ligne dans une table, celle-ci est rangée dans l'ordre alphabétique ou croissant. En vrai, non: ta nouvelle ligne est simplement mise à la fin. Du coup, sans index, tes recherches doivent regarder chaque lignes de la première à la dernière.

            Enfin des bases comme mysql sont/étaient(?) pauvres en fonctionnalités par choix de pouvoir traiter rapidement de grosses masse de données, contrairement à certaines autres (oracle,posgresql..) qui pour offrir plus de fonctionnalités avait des temps de recherche moins performant hors tweekenning complexe à mettre en oeuvre et adapté au cas par cas à la suite de tests et tests en tout sens.

            Oui, MySQL est toujours assez pauvre, mais ca s'améliore beaucoup avec le temps. Malgré tout, il y aura toujours un fossé entre MySQL et Postgres ou Oracle que tu cites.
            Pour les temps de recherche, je ne suis pas d'accord. Globalement ils se valent à peu près (hors tweakening comme tu dis :) sinon MySQL est à la ramasse)

            Et pour le adapté au cas par cas, oui, malheureusement, chaque BDD doit être adaptée au cas par cas… Il n'y a jamais de solution universelle.

            • [^] # Commentaire supprimé

              Posté par  . Évalué à 3.

              Ce commentaire a été supprimé par l’équipe de modération.

              • [^] # Re:

                Posté par  . Évalué à 2. Dernière modification le 16 avril 2014 à 10:24.

                Tout à fait d'accord.
                Je ne connais pas MS SQL, mais effectivement, les index cluster permettent d'ordonner tes données. Je me suis peut-être mal exprimé dans ma réponse précédente (un peu au dessus du passage que tu cites, je parle des clustered index de mysql).

                Ceci dit, merci pour tes explications, car je ne connais pas très bien ces types d'index.

  • # MySQL ou sqlite ?

    Posté par  (site web personnel) . Évalué à 6.

    Tu parles d'optimiser du MySQL pour un projet qui utilise sqlite3. T'es sûr que tu ne veux pas de l'aide sur Postgresql ?

  • # La règle sainte de l'optimisation de base

    Posté par  . Évalué à 2.

    "Quand ta base dans le sac sera par optimiser tes requêtes et tes indexes tu commencera."

  • # Commentaire supprimé

    Posté par  . Évalué à 2.

    Ce commentaire a été supprimé par l’équipe de modération.

    • [^] # Re: des pistes...

      Posté par  (site web personnel) . Évalué à 1. Dernière modification le 16 avril 2014 à 22:50.

      Merci pour ta longue réponse !

      "form" peut contenir plusieurs enregistrements identiques. Par exemple, "vis" peut venir du verbe "voir" (1 enregistrement) ou du verbe "vivre" (un autre enregistrement).

      Je cherche avant tout à optimiser la recherche, pas l'insertion.

      J'ai remplacé mon type TEXT par un VARCHAR(255).

      La recherche doit se faire en tenant compte des accents, de la casse, … Je ne peux pas simplifier le texte à ce niveau.

      Je donne ici un exemple d'image texte de la base de données. Les champs sont séparés par un carré.

      L'unique requête SQL que je pensais autoriser était très simplement :

                  sql_order = "SELECT sourceword, inflection_id, context, searchfreq "
                        "FROM forms "
                        "WHERE form=?"

      Merci de t'être penché sur mon projet !

      Trust the Python !

Suivre le flux des commentaires

Note : les commentaires appartiennent à celles et ceux qui les ont postés. Nous n’en sommes pas responsables.