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 :

Requête sur index inversé - Optimisation


Sujet :

Requêtes MySQL

  1. #1
    Membre confirmé
    Profil pro
    Inscrit en
    Février 2004
    Messages
    496
    Détails du profil
    Informations personnelles :
    Localisation : Belgique

    Informations forums :
    Inscription : Février 2004
    Messages : 496
    Points : 585
    Points
    585
    Par défaut Requête sur index inversé - Optimisation
    Bonjour,

    j'exécute une requête pour récupérer une liste de fichiers contenant tous les mots de la recherche.

    La structure de mes tables est la suivante :

    words
    id
    word (index)

    words_files
    id
    word_id (index)
    file_id (index)

    Et la requête utilisée :
    Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    SELECT WF.file_id 
    FROM words_files WF 
    JOIN words W ON WF.word_id = W.id 
    JOIN (SELECT file_id FROM words_files WF INNER JOIN words W ON WF.word_id = W.id WHERE word LIKE "mot1" ) AS t1 ON WF.file_id = t1.file_id 
    WHERE word LIKE "mot2"

    Je fais donc une intersection entre chaque requête qui trouve les fichiers dans lesquels un mot est présent.. mais voilà, à partir d'un certain volume de données cette requête devient extrêmement lente, surtout à cause de la sous-requête il me semble (donc ça empirera au plus il y aura de mots dans la recherche)..

    Donc si vous avez des idées pour soit optimiser cette requête, soit pour le faire d'une autre façon, ça m'aiderait bcp

    Merci

  2. #2
    Modérateur

    Profil pro
    dba
    Inscrit en
    Janvier 2010
    Messages
    5 643
    Détails du profil
    Informations personnelles :
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : dba

    Informations forums :
    Inscription : Janvier 2010
    Messages : 5 643
    Points : 13 092
    Points
    13 092
    Par défaut
    bonjour,

    Vous pouvez faire quelque chose comme ceci (a tester/adapter) :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
     
    SELECT WF.file_id 
    FROM words_files WF 
    JOIN words W 
        ON WF.word_id = W.id 
    WHERE word IN ('mot1', 'mot2')
    GROUP BY WF.file_id
    HAVING COUNT(*) = 2

  3. #3
    Membre confirmé
    Profil pro
    Inscrit en
    Février 2004
    Messages
    496
    Détails du profil
    Informations personnelles :
    Localisation : Belgique

    Informations forums :
    Inscription : Février 2004
    Messages : 496
    Points : 585
    Points
    585
    Par défaut
    Merci pour votre réponse, malheureusement je ne vois aucune amélioration au niveau des performances.

    Par contre j'ai oublié de préciser que je dois pouvoir utiliser les wildcards, ce que l'on ne peut pas faire avec le statement IN, de plus je ne pourrai pas prédire le having count(*) = x si j'utilise des LIKE à la place de IN..

    En court, je veux récupérer les fichiers contenant (le mot "mot1" OU ceux commençant par "mot1") ET (le mot "mot2" OU ceux commençant par "mot2")

  4. #4
    Expert éminent sénior Avatar de Artemus24
    Homme Profil pro
    Agent secret au service du président Ulysses S. Grant !
    Inscrit en
    Février 2011
    Messages
    6 346
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Agent secret au service du président Ulysses S. Grant !
    Secteur : Finance

    Informations forums :
    Inscription : Février 2011
    Messages : 6 346
    Points : 18 958
    Points
    18 958
    Par défaut
    Salut billoum.

    Citation Envoyé par billoum
    En court, je veux récupérer les fichiers contenant (le mot "mot1" OU ceux commençant par "mot1") ET (le mot "mot2" OU ceux commençant par "mot2")
    Vu comme c'est formulé, le résultat de ta demande sera toujours vide. Ce n'est pas un 'ET' (ce qui est en rouge) qu'il faut mettre mais un 'OU'.

    J'ai créé un jeu d'essai afin de tester ta demande, que voici :
    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
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    113
    114
    --------------
    SET AUTOCOMMIT = 0
    --------------
     
    --------------
    START TRANSACTION
    --------------
     
    --------------
    DROP DATABASE IF EXISTS `base`
    --------------
     
    --------------
    CREATE DATABASE `base`
            DEFAULT CHARACTER SET `latin1`
            DEFAULT COLLATE       `latin1_general_ci`
    --------------
     
    --------------
    DROP TABLE IF EXISTS `words`
    --------------
     
    --------------
    CREATE TABLE `words`
    (
      `id`       integer unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
      `word`     char(10)         NOT NULL,
      INDEX `idx` (`word`)
    ) ENGINE=InnoDB
      DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
      ROW_FORMAT=COMPRESSED
    --------------
     
    --------------
    INSERT INTO `words` (`word`) VALUES
    ('un'),('deux'),('trois'),('un'),('quatre'),('trois'),('un')
    --------------
     
    --------------
    select * from words order by id
    --------------
     
    +----+--------+
    | id | word   |
    +----+--------+
    |  1 | un     |
    |  2 | deux   |
    |  3 | trois  |
    |  4 | un     |
    |  5 | quatre |
    |  6 | trois  |
    |  7 | un     |
    +----+--------+
    --------------
    DROP TABLE IF EXISTS `words_file`
    --------------
     
    --------------
    CREATE TABLE `words_file`
    (
      `id`       integer unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
      `word_id`  integer unsigned NOT NULL,
      `file_id`  integer unsigned NOT NULL,
      CONSTRAINT `FOREIGN` FOREIGN KEY (`word_id`) REFERENCES `words` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
    ) ENGINE=InnoDB
      DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
      ROW_FORMAT=COMPRESSED
    --------------
     
    --------------
    INSERT INTO `words_file` (`word_id`,`file_id`) VALUES
    (1, 1),(1, 4),
    (2, 3),(2, 7),(2, 9),
    (3, 2),(3, 5),
    (4, 1),(4, 7),
    (5, 6),(5, 8),(5, 9),
    (6, 3),(6, 5),
    (7, 1),(7, 4)
    --------------
     
    --------------
    select * from words_file order by id
    --------------
     
    +----+---------+---------+
    | id | word_id | file_id |
    +----+---------+---------+
    |  1 |       1 |       1 |
    |  2 |       1 |       4 |
    |  3 |       2 |       3 |
    |  4 |       2 |       7 |
    |  5 |       2 |       9 |
    |  6 |       3 |       2 |
    |  7 |       3 |       5 |
    |  8 |       4 |       1 |
    |  9 |       4 |       7 |
    | 10 |       5 |       6 |
    | 11 |       5 |       8 |
    | 12 |       5 |       9 |
    | 13 |       6 |       3 |
    | 14 |       6 |       5 |
    | 15 |       7 |       1 |
    | 16 |       7 |       4 |
    +----+---------+---------+
    --------------
    COMMIT
    --------------
     
    --------------
    SET AUTOCOMMIT = 1
    --------------
     
     
    Appuyez sur une touche pour continuer...
    Comme tu peux le constater, et en plus des 'primary key', j'ai ajouté :
    --> une clef étrangère entre la colonne 'word_id' de la table words_file et entre la colonne 'id' de words.
    --> un index sur la colonne 'word' de words.

    Voici une solution avec un 'like' et un 'group by' ainsi qu'un 'order by'.
    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
    18
    19
    20
    21
    22
    23
    24
    --------------
    select tb2.file_id
     
    from       words      as tb1
    inner join words_file as tb2
    on tb2.word_id = tb1.id
    where tb1.word like 'un%'
    or    tb1.word like 'trois%'
    group by tb2.file_id
    order by tb2.file_id
    --------------
     
    +---------+
    | file_id |
    +---------+
    |       1 |
    |       2 |
    |       3 |
    |       4 |
    |       5 |
    |       7 |
    +---------+
     
    Appuyez sur une touche pour continuer...
    Ce jeu d'essai n'est pas représentatif des questions de performances.

    @+
    Si vous êtes de mon aide, vous pouvez cliquer sur .
    Mon site : http://www.jcz.fr

  5. #5
    Membre confirmé
    Profil pro
    Inscrit en
    Février 2004
    Messages
    496
    Détails du profil
    Informations personnelles :
    Localisation : Belgique

    Informations forums :
    Inscription : Février 2004
    Messages : 496
    Points : 585
    Points
    585
    Par défaut
    Merci de ton aide Artemus24, mais malheureusement il s'agit bien de récupérer les fichiers contenant un mot commençant par "mot1" ET un commençant par "mot2"

    Par ex. si je fais une recherche sur "the cat", et les fichiers :

    f1 = "the cat"
    f2 = "these cats"
    f3 = "their cat"
    f4 = "that cat"

    Il devrait me trouver les fichiers 1, 2 et 3 mais pas le 4, et la requête que j'ai postée dans mon premier com' le fait bien.

    Mon soucis se situe vraiment au niveau de l'optimisation, j'avais déjà mis des index sur word ainsi que sur les clefs étrangères, mais ça ne suffit pas, le problème vient vraiment du fait que j'utilse des sous-réquêtes, c'est pour cela que je me demandais s'il n'y avait pas une autre façon de faire..

  6. #6
    Expert confirmé
    Profil pro
    Inscrit en
    Août 2008
    Messages
    2 947
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Août 2008
    Messages : 2 947
    Points : 5 846
    Points
    5 846
    Par défaut
    Je ne sais pas si j'ai bien compris, que donne cette requête :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    select WF.file_id
      from words_files wf
      join (select word_id
              from words
             where word like 'mot1%'
             union all
            select word_id
              from words
             where word like 'mot2%'
           ) w
        on W.id = WF.word_id

  7. #7
    Membre confirmé
    Profil pro
    Inscrit en
    Février 2004
    Messages
    496
    Détails du profil
    Informations personnelles :
    Localisation : Belgique

    Informations forums :
    Inscription : Février 2004
    Messages : 496
    Points : 585
    Points
    585
    Par défaut
    Il me semble que cette requête récupère tous les fichiers contenant un mot commençant par "mot1" ou commençant par "mot2".

    Avec mon exemple précédent le fichier f4 serait sélectionné, alors qu'il ne devrait pas l'être.

    Merci

  8. #8
    Expert confirmé
    Profil pro
    Inscrit en
    Août 2008
    Messages
    2 947
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Août 2008
    Messages : 2 947
    Points : 5 846
    Points
    5 846
    Par défaut
    Fournissez un jeu de données qui correspond à la modélisation présentée, dans l'état quel est le mot1 et quel est le mot2
    Moi je comprends que f1, f2, et f3 sont renvoyés car ils commencent par "the"...

  9. #9
    Membre confirmé
    Profil pro
    Inscrit en
    Février 2004
    Messages
    496
    Détails du profil
    Informations personnelles :
    Localisation : Belgique

    Informations forums :
    Inscription : Février 2004
    Messages : 496
    Points : 585
    Points
    585
    Par défaut
    Si on a mot1 = "the" et mot2 = "cat", avec les fichiers :

    f1 = "blabla cat blabla the blabla"
    f2 = "blabla these blabla cats blabla "
    f3 = "blabla their blabla cat blabla"
    f4 = "blabla that blabla cat blabla"


    f1, f2 et f3 sont renvoyés parce qu'ils contiennent au moins un mot commençant par the ET un mot commençant par cat, f4 a bien un mot commençant par cat mais pas de mot commençant par the

    Dans la bdd on aurait :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    words 
    id    word
     1    the
     2    cat
     3    their
     4    that
     5    cats
     6    these
    Et :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    words_files 
    id  word_id  file_id
     1     1         1
     2     2         1
     3     2         3
     4     2         4
     5     3         3
     6     4         4
     7     5         2
     8     6         2

  10. #10
    Expert éminent sénior Avatar de Artemus24
    Homme Profil pro
    Agent secret au service du président Ulysses S. Grant !
    Inscrit en
    Février 2011
    Messages
    6 346
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Agent secret au service du président Ulysses S. Grant !
    Secteur : Finance

    Informations forums :
    Inscription : Février 2011
    Messages : 6 346
    Points : 18 958
    Points
    18 958
    Par défaut
    Salut billoum.

    Comme vous n'avez pas donné d'exemple au préalable, j'avais compris que vous recherchiez des mots séparés et non liés.

    Vous pouvez écrire votre sélection en mettant "where tb1.word like '%mot1%mot2%'".
    Vous obtiendrez des sélections ayant 'mot1' d'abord, suivit par 'mot2'. Cela va correspondre l'obtention de 'f2' et 'f3'.

    Vous serez obligé ensuite de faire un "where tb1.word like '%mot2%mot1%'". C'est-à-dire permuter les deux chaînes de caractères.
    Vous obtiendrez des sélections ayant 'mot2' d'abord, suivit par 'mot1'. Cela va correspondre à la sélection de 'f1'.

    La gestion des chaînes de caractères par la clause like est peu adapté pour ce que vous voulez faire.
    Les performances de vos recherches seront très très dégradées.

    @+
    Si vous êtes de mon aide, vous pouvez cliquer sur .
    Mon site : http://www.jcz.fr

  11. #11
    Expert confirmé
    Profil pro
    Inscrit en
    Août 2008
    Messages
    2 947
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Août 2008
    Messages : 2 947
    Points : 5 846
    Points
    5 846
    Par défaut
    Artemus24, je pense que sa phrase est déjà découpée en mot.

    Je ne sais pas si c'est mieux :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    SELECT WF.file_id 
      FROM words_files WF
     where exists (select 1
                     from words W1
                    where W1.id = WF.word_id
                      and w1.word LIKE "mot1%")
       and exists (select 1
                     from words W2
                    where W2.id = WF.word_id
                      and w2.word LIKE "mot2%")
    Sinon avec une jointure, j'ai l'impression qu'il y a un accès de trop sur words_files dans la sous-requête :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    SELECT WF.file_id 
      FROM words_files WF
      JOIN words W1 ON WF.word_id = W1.id 
      JOIN words W2 ON WF.word_id = W2.id 
     WHERE w1.word LIKE "mot1%"
       and w2.word LIKE "mot2%"
    Après je ne sais pas si c'est vraiment mieux.

  12. #12
    Expert éminent sénior Avatar de Artemus24
    Homme Profil pro
    Agent secret au service du président Ulysses S. Grant !
    Inscrit en
    Février 2011
    Messages
    6 346
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Agent secret au service du président Ulysses S. Grant !
    Secteur : Finance

    Informations forums :
    Inscription : Février 2011
    Messages : 6 346
    Points : 18 958
    Points
    18 958
    Par défaut
    Salut à tous.

    Je suis parti sur une autre idée que celle du 'like'. Celle du FullText !
    Voici la base de données avec son exemple que j'ai repris.
    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
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    --------------
    SET AUTOCOMMIT = 0
    --------------
     
    --------------
    START TRANSACTION
    --------------
     
    --------------
    DROP DATABASE IF EXISTS `base`
    --------------
     
    --------------
    CREATE DATABASE `base`
            DEFAULT CHARACTER SET `latin1`
            DEFAULT COLLATE       `latin1_general_ci`
    --------------
     
    --------------
    DROP TABLE IF EXISTS `test`
    --------------
     
    --------------
    CREATE TABLE `test` (
      `id`          int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
      `texte`       varchar(255)     NULL DEFAULT NULL,
      FULLTEXT KEY `ft` (`texte`)
    ) ENGINE=InnoDB
      DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
      ROW_FORMAT=COMPRESSED
    --------------
     
    --------------
    INSERT INTO `test` (`texte`) VALUES
    ('blabla cat   blabla the  blabla'),
    ('blabla dog   blabla the  blabla'),
    ('blabla these blabla cats blabla'),
    ('blabla these blabla dog  blabla'),
    ('blabla their blabla cat  blabla'),
    ('blabla that  blabla cat  blabla')
    --------------
     
    --------------
    select * from test
    --------------
     
    +----+---------------------------------+
    | id | texte                           |
    +----+---------------------------------+
    |  1 | blabla cat   blabla the  blabla |
    |  2 | blabla dog   blabla the  blabla |
    |  3 | blabla these blabla cats blabla |
    |  4 | blabla these blabla dog  blabla |
    |  5 | blabla their blabla cat  blabla |
    |  6 | blabla that  blabla cat  blabla |
    +----+---------------------------------+
    --------------
    COMMIT
    --------------
     
    --------------
    SET AUTOCOMMIT = 1
    --------------
     
     
    Appuyez sur une touche pour continuer...
    Voici le résultat que j'obtiens.
    Je ne comprends pas très bien pourquoi je n'obtiens pas le résultat attendu, à savoir les lignes 1, 3 et 5.
    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
    --------------
    select *
    from test
    where MATCH (texte) AGAINST ('+the* +cat*')
    order by id
    --------------
     
    +----+---------------------------------+
    | id | texte                           |
    +----+---------------------------------+
    |  1 | blabla cat   blabla the  blabla |
    |  5 | blabla their blabla cat  blabla |
    |  6 | blabla that  blabla cat  blabla |
    +----+---------------------------------+
     
    Appuyez sur une touche pour continuer...
    @+
    Si vous êtes de mon aide, vous pouvez cliquer sur .
    Mon site : http://www.jcz.fr

  13. #13
    Membre confirmé
    Profil pro
    Inscrit en
    Février 2004
    Messages
    496
    Détails du profil
    Informations personnelles :
    Localisation : Belgique

    Informations forums :
    Inscription : Février 2004
    Messages : 496
    Points : 585
    Points
    585
    Par défaut
    @ skuatamad : non ça ne donne aucun résultat, je pense que ces requêtes récupèrent des enregistrements de words_files qui sont liés à "mot1" ET à "mot2", hors un enregistrement de words_files ne peut-être lié qu'à un seul mot

    @ Artemus24 : malheureusement l'index FullText n'est pas un option, je l'avais déjà utilisé mais dans ce cas-ci je dois faire avec la structure de données que je vous ai présentée.. Sinon pour ta requête je pense que tu dois utiliser le BOOLEAN MODE pour pouvoir utiliser les opérateurs + et * :

    Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    select *
    from test
    where MATCH (texte) AGAINST ('+the* +cat*'  IN BOOLEAN MODE)
    order by id

  14. #14
    Expert confirmé
    Homme Profil pro
    Inscrit en
    Mai 2002
    Messages
    3 173
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 41
    Localisation : France, Rhône (Rhône Alpes)

    Informations forums :
    Inscription : Mai 2002
    Messages : 3 173
    Points : 5 345
    Points
    5 345
    Par défaut
    Bonjour,

    La requete générique pour gérer ce genre de cas à déjà été donnée par aieuuu il y a quelques postes (un peu modifiée ici) :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
     
    SELECT WF.file_id 
    FROM words_files WF 
    JOIN words W 
        ON WF.word_id = W.id 
    WHERE word IN ('mot1', 'mot2')
    GROUP BY WF.file_id
    HAVING COUNT(distinct word_id) > 1

    Au vue du besoin du like, celle de skuatamad un peu arrangée doit donner le bon résultat :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
     
    select WF.file_id
      from words_files wf
      join (select word_id
              from words
             where word like 'mot1%'
             union all
            select word_id
              from words
             where word like 'mot2%'
           ) w
        on W.id = WF.word_id
    group by WF.file_id
    having count(distinct word_id) > 1

    Sa deuxième requête, un peu modifier donnera aussi un résultat :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
     
    SELECT WF.file_id 
      FROM words_files WF
     where exists (select 1
                     from words W1
                    where W1.id = WF.word_id
                      and w1.word LIKE "mot1%")
       or exists (select 1
                     from words W2
                    where W2.id = WF.word_id
                      and w2.word LIKE "mot2%")
    group by WF.file_id
    having count(distinct word_id) > 1
    Votre version initiale fonctionne aussi.

    Dans tous les cas, vous atteindrez à un moment donné un problème de perf.

    Celui-ci peut être minimisé jusqu’à un certain point si vous utilisez uniquement un test du type like 'ma_valeur%'

    En effet un index pourra être utilisé en range scan pour résoudre ce cas.

  15. #15
    Expert éminent sénior Avatar de Artemus24
    Homme Profil pro
    Agent secret au service du président Ulysses S. Grant !
    Inscrit en
    Février 2011
    Messages
    6 346
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Agent secret au service du président Ulysses S. Grant !
    Secteur : Finance

    Informations forums :
    Inscription : Février 2011
    Messages : 6 346
    Points : 18 958
    Points
    18 958
    Par défaut
    Salut à tous.

    @ punkoff : cette solution a déjà été envisagé et elle ne fonctionne pas. Pourquoi ?

    1) billoum dit qu'il utilise des mots qui sont liés dans la phrase et non des mots disjoints comme vous le faites.
    Dans le inner join, vous sélectionner les lignes qui ont soit le premier mot ou soit le second mot.
    Or Billoum a bien précisé que c'est un et. Donc cela ne convient pas.

    2) le mot ne commence pas obligatoirement au début de la ligne mais il peut être présent n'importe où.
    Voir son exemple dans le message #9.
    De plus, l'ordre n'a pas d'importance, ce qui signifie que 'mot1' peut être avant ou après 'mot2'.
    Cela nécessite de faire des combinaisons :
    --> deux mots = 2! (factoriel de 2), soit 2.
    --> trois mots = 3!, soit 6.
    Et ainsi de suite.

    3) comme la recherche du mot se fait n'importe où dans la ligne, on aura un énorme problème de performance.
    Citation Envoyé par punkoff
    Dans tous les cas, vous atteindrez à un moment donné un problème de perf.
    La multiplication des subdivisions de l'union n'est pas la solution car le temps du traitement sera de plus en plus long.

    4) je voie que vous avez corrigé l'erreur de 'aieeeuuuuu'. Voici son regroupement :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    GROUP BY WF.file_id
    HAVING COUNT(*) = 2
    La comptabilisation ne peut pas se faire sur le 'file_id', mais sur le 'word_id' de la table 'words_files'.
    Car en faisant un test sur le nombre d'occurrences de file_id, on peut sélectionner des lignes qui ne doivent pas l'être.
    Admettons que pour 'the dot' vous avez un file_id de 99.
    Et pour 'category', vous avez aussi un file_id de 99.
    Vous avez deux occurrences pour ce 99, et vous allez sélectionner les lignes qui en principe ne doivent pas l'être.

    et voici le votre :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    GROUP BY WF.file_id
    HAVING COUNT(distinct word_id) > 1
    Cette solution est meilleur.
    Mais ce genre de test ne sert rien car le problème se porte sur la sélection des lignes dans la table 'words'.

    @ billoum : je me doutais bien que le 'FullText' ne pouvait pas convenir.
    J'ai essayé plusieurs écritures, mais je ne sais pas pourquoi, il ne veux pas traiter le mot 'the' ???
    A chaque forme d'écriture que j'ai tenté de faire, il ne trouve jamais le mot 'the'. Et je ne sais pas pourquoi.

    La solution que je propose est de scinder en deux parties le traitement pour des questions de performances.
    1) extraire les lignes de la table 'words' selon les critères que tu recherches. J'utilise une table temporaire.
    2) faire la jointure.

    Il faudra tester ceci :
    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
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    --------------
    create temporary table extract like words
    --------------
     
    --------------
    INSERT INTO `extract`
    SELECT id, word
    FROM words
    WHERE word like '%the%'
    AND   word like '%cat%'
    --------------
     
    --------------
    select * from extract
    --------------
     
    +----+------------+
    | id | word       |
    +----+------------+
    |  1 | the cat    |
    |  3 | their cat  |
    |  2 | these cats |
    +----+------------+
    --------------
    select tb1.file_id
    from       words_files as tb1
    inner join extract     as tb2
    on tb2.id = tb1.word_id
    group by tb1.file_id
    --------------
     
    +---------+
    | file_id |
    +---------+
    |       1 |
    |       3 |
    |       4 |
    +---------+
     
    Appuyez sur une touche pour continuer...
    Je suis reparti sur ton mini exemple. Je me suis assuré que le 'insert' ne soit pas trop couteux.
    Il y a autant de lectures que de lignes. Je ne peux pas faire mieux. Par l'union, le nombre de lectures aurait explosé.
    Il y a bien moins d'écritures car on ne sélectionne pas toutes les lignes.

    De plus, tu peux construire ta sélection comme tu le désires.
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    where word like '%the%'
    AND   word like '%cat%'
    Ici, j'ai sélectionné le mot 'the' et le mot 'cat', comme dans ton exemple et comme tu le désires.
    La quatrième ligne n'a pas été sélectionnée.

    Peux-tu me confirmé que cette approche est plus performante que ta solution ?

    Dans la table temporaire, tu auras peu de ligne, et de ce fait, la jointure durera bien moins longtemps.

    P.S.: c'est moi qui met des '+1' partout.

    @+
    Si vous êtes de mon aide, vous pouvez cliquer sur .
    Mon site : http://www.jcz.fr

  16. #16
    Membre confirmé
    Profil pro
    Inscrit en
    Février 2004
    Messages
    496
    Détails du profil
    Informations personnelles :
    Localisation : Belgique

    Informations forums :
    Inscription : Février 2004
    Messages : 496
    Points : 585
    Points
    585
    Par défaut
    En fait il s'agit bien de mots indépendants, selon cette structure de données :

    words qui listent individuellement tous les mots présents dans mes textes
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    id    word
     1    the
     2    cat
     3    their
     4    that
     5    cats
     6    these
    words_files qui met en relation les mots et les fichiers
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    id  word_id  file_id
     1     1         1
     2     2         1
     3     2         3
     4     2         4
     5     3         3
     6     4         4
     7     5         2
     8     6         2
    Je vais voir ce que ça donne avec les tables temporaires, je ne saurai pas dire comme cela si cela représente un gain de performance ou pas, je vous tiens au courant.

    Sinon il va me falloir limiter un peu les fonctionnalités de ma recherche ou bien trouver d'autre pistes d'optimisation parce qu'apparemment je ne pourrai pas le faire au niveau de la requête de sélection..

    En tt cas merci à tous pour le temps que vous avez consacré à mon problème

    PS @ Artemus24 : je viens d'y penser mais le fulltext ne trouve pas le mot "the" parce qu'il fait partie des mots qui ne sont pas recherchés, les stopwords

  17. #17
    Expert éminent
    Avatar de StringBuilder
    Homme Profil pro
    Chef de projets
    Inscrit en
    Février 2010
    Messages
    4 146
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Chef de projets
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Février 2010
    Messages : 4 146
    Points : 7 388
    Points
    7 388
    Billets dans le blog
    1
    Par défaut
    Que donne cette requête ?

    Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
     
    select *
    from files f
    where exists (
    	select null from words w inner join word_files wf on wf.id_word = w.id and wf.id_file = f.id where w.word like 'cat%'
    )
    and exists (
    	select null from words w inner join word_files wf on wf.id_word = w.id and wf.id_file = f.id where w.word like 'the%'
    );

    Car j'ai l'impression que pour la plupart des solutions proposées, si on a un fichier "5" qui contient "cat cats", alors il est retourné, alors qu'on n'a trouvé qu'un seul des deux termes recherchés.
    On ne jouit bien que de ce qu’on partage.

  18. #18
    Expert éminent sénior Avatar de Artemus24
    Homme Profil pro
    Agent secret au service du président Ulysses S. Grant !
    Inscrit en
    Février 2011
    Messages
    6 346
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Agent secret au service du président Ulysses S. Grant !
    Secteur : Finance

    Informations forums :
    Inscription : Février 2011
    Messages : 6 346
    Points : 18 958
    Points
    18 958
    Par défaut
    Salut à tous.

    Citation Envoyé par billoum
    je viens d'y penser mais le fulltext ne trouve pas le mot "the" parce qu'il fait partie des mots qui ne sont pas recherchés, les stopwords
    J'ai déjà utilise le 'Full Text Search' maus uniquement en français et je n'ai rencontré aucun problème de rejet de mots.
    Merci de me l'apprendre, mais je ne savais même pas que les 'stopwords' existaient.
    Alors j'ai fait une petite recherche sous google et j'ai trouvé comme le désactiver dans mon environnement.

    Deux cas, à mettre dans le fichier 'my.ini' pour windows, dans la section [wampmysqld] :
    1) soit avec le moteur 'innodb' :
    --> innodb_ft_enable_stopword = off
    --> innodb_ft_max_token_size = 10
    --> innodb_ft_min_token_size = 0

    2) soit avec le moteur 'myisam' :
    --> ft_max_word_len = 10
    --> ft_min_word_len = 0
    --> ft_stopword_file = ""

    Maintenant, le mot 'the' n'est plus rejeté dans les recherches.

    @ StringBuilder : j'ai réadapté ta requête à mon exemple, mais elle produit une erreur de syntaxe :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    --------------
    select * from files as f
    where exists (select null from words w inner join words_files wf on wf.word_id = w.id and wf.file_id1 = f.id where w.word like 'cat%')
    and   exists (select null from words w inner join words_files wf on wf.word_id = w.id and wf.file_id1 = f.id where w.word like 'the%')
    --------------
     
    ERROR 1054 (42S22) at line 22: Champ 'f.id' inconnu dans on clause
    J'ai corrigé l'erreur car dans la clause 'on', on ne peut mettre que des noms de colonnes qui sont en relation avec les tables auquelles elles se réfèrent.
    Ce qui donne :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    --------------
    select * from files as f
    where exists (select null from words w inner join words_files wf on wf.word_id = w.id2 where wf.file_id = f.id1 and w.word like 'cat%')
    and   exists (select null from words w inner join words_files wf on wf.word_id = w.id2 where wf.file_id = f.id1 and w.word like 'the%')
    --------------
     
    +-----+------------+
    | id1 | text       |
    +-----+------------+
    |   1 | cat the    |
    |   2 | these cats |
    |   3 | their cat  |
    +-----+------------+
    @ tous : jusqu'à présent, j'étais parti sur le problème proposé par Billoum dans son premier message.
    A savoir deux tables, dont l'une 'words' contenait dans chaque ligne le texte, comme ci-après :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    1 : 'cat the'
    2 : 'these cats'
    3 : 'their cat'
    4 : 'that cat'
    Je reprends tout ce qui a été dit depuis le début du sujet afin d'être conforme à la problématique de Billoum.
    J'ai créé trois tables que voici :
    --> table 'files' : id1 et text (contient les phrases ci-dessus comme exemple à analyser).
    --> table 'words' : id2 et word (contient tous les mots présents dans 'text').
    --> table 'words_files' : id3, word_id (qui pointe vers id2) et file_id (qui pointe vers id1).

    La requête est la suivante :
    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
    18
    19
    20
    21
    22
    23
    24
    25
    --------------
    select *
    from       files       as  f
    inner join words_files as wf
    on wf.file_id = f.id1
     
    inner join words       as w
    on   w.id2 = wf.word_id
    and (w.word like 'the%'
    or   w.word like 'cat%')
     
    order by wf.file_id
    --------------
     
    +-----+------------+-----+---------+---------+-----+-------+
    | id1 | text       | id3 | word_id | file_id | id2 | word  |
    +-----+------------+-----+---------+---------+-----+-------+
    |   1 | cat the    |   1 |       1 |       1 |   1 | the   |
    |   1 | cat the    |   2 |       2 |       1 |   2 | cat   |
    |   2 | these cats |   5 |       3 |       2 |   3 | these |
    |   2 | these cats |   6 |       4 |       2 |   4 | cats  |
    |   3 | their cat  |   3 |       2 |       3 |   2 | cat   |
    |   3 | their cat  |   7 |       5 |       3 |   5 | their |
    |   4 | that cat   |   4 |       2 |       4 |   2 | cat   |
    +-----+------------+-----+---------+---------+-----+-------+
    Maintenant, j'ajoute le 'group by' et le 'having', et on trouve le résultat suivant :
    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
    18
    19
    20
    21
    22
    23
    --------------
    select f.id1, f.text
    from       files       as  f
    inner join words_files as wf
    on wf.file_id = f.id1
     
    inner join words       as w
    on   w.id2 = wf.word_id
    and (w.word like 'the%'
    or   w.word like 'cat%')
     
    group by wf.file_id
    having count(f.id1) > 1
    order by wf.file_id
    --------------
     
    +-----+------------+
    | id1 | text       |
    +-----+------------+
    |   1 | cat the    |
    |   2 | these cats |
    |   3 | their cat  |
    +-----+------------+
    Résultat conforme à ce que l'on attend. Et conforme à la solution proposé par 'aieeeuuuuu' dans un premier temps et repris par 'punkoff'.

    A titre indicatif, je donne mon jeu d'essai :
    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
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    --------------
    select * from files       order by id1
    --------------
     
    +-----+------------+
    | id1 | text       |
    +-----+------------+
    |   1 | cat the    |
    |   2 | these cats |
    |   3 | their cat  |
    |   4 | that cat   |
    +-----+------------+
    --------------
    select * from words       order by id2
    --------------
     
    +-----+-------+
    | id2 | word  |
    +-----+-------+
    |   1 | the   |
    |   2 | cat   |
    |   3 | these |
    |   4 | cats  |
    |   5 | their |
    |   6 | that  |
    +-----+-------+
    --------------
    select * from words_files order by id3
    --------------
     
    +-----+---------+---------+
    | id3 | word_id | file_id |
    +-----+---------+---------+
    |   1 |       1 |       1 |
    |   2 |       2 |       1 |
    |   3 |       2 |       3 |
    |   4 |       2 |       4 |
    |   5 |       3 |       2 |
    |   6 |       4 |       2 |
    |   7 |       5 |       3 |
    |   8 |       6 |       4 |
    +-----+---------+---------+
    La solution que je propose est de créer une table temporaire sur la table 'words'.
    Cette table temporaire va contenir toutes les lignes qui seront sélectionnées selon les mot 'the%' et 'cat%'.
    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
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    --------------
    drop temporary table `extract`
    --------------
     
    ERROR 1051 (42S02) at line 25: Table 'base.extract' inconnue
    --------------
    create temporary table `extract` like `words`
    --------------
     
    --------------
    insert into `extract`
    select id2, word
    from words
    where word like 'the%'
    or    word like 'cat%'
    --------------
     
    --------------
    select * from extract order by id2
    --------------
     
    +-----+-------+
    | id2 | word  |
    +-----+-------+
    |   1 | the   |
    |   2 | cat   |
    |   3 | these |
    |   4 | cats  |
    |   5 | their |
    +-----+-------+
    La ligne 6 contenant le mot 'that' n'a pas été sélectionné.
    Je suppose que la table 'words' étant énorme, la création de la table temporaire sera plus rapide.
    On ne fait qu'un seul balayage et non deux comme sur les autres exemples (un balayage sur la chaine 'the%' et un autre sur 'cat%'.
    C'est sur ce point que j'aimerai savoir si l'on gagne du temps.

    @+
    Si vous êtes de mon aide, vous pouvez cliquer sur .
    Mon site : http://www.jcz.fr

  19. #19
    Expert confirmé
    Homme Profil pro
    Inscrit en
    Mai 2002
    Messages
    3 173
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 41
    Localisation : France, Rhône (Rhône Alpes)

    Informations forums :
    Inscription : Mai 2002
    Messages : 3 173
    Points : 5 345
    Points
    5 345
    Par défaut
    Citation Envoyé par Artemus24 Voir le message
    Salut à tous.

    @ punkoff : cette solution a déjà été envisagé et elle ne fonctionne pas. Pourquoi ?
    Les solutions cités dans mon poste fonctionnent au vu du jeu de donnée et du besoin exprimé.

    Si ca fonctionne pas c'est que l'OP s'exprime mal, à lui de reformuler.

  20. #20
    Expert éminent
    Avatar de StringBuilder
    Homme Profil pro
    Chef de projets
    Inscrit en
    Février 2010
    Messages
    4 146
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Chef de projets
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Février 2010
    Messages : 4 146
    Points : 7 388
    Points
    7 388
    Billets dans le blog
    1
    Par défaut
    J'insiste sur le fait que filtrer les lignes avec count(distinct words.id) > 1 n'est pas la solution !

    Si j'ai une phrase "cat cats kitten", alors qune rechercher sur "the cat" va retourner 2.
    Donc on va retenir la ligne.

    Pourtant, "the" n'a pas été trouvé. On a juste trouvé deux variantes de "cat" !

    On ne peut pas faire autrement que de retenir les lignes où on a trouvé au moins 1 fois chacun des termes recherchés.

    Bon, ceci dit, des réponses à base de full text ont été proposées. Pourquoi cette solution, à la fois performante et simple est-elle écartée ? Car refaire en SQL un moteur d'indexation/recherche textuel, je vois pas trop l'intérêt... surtout quand le SGBD qu'on utilise en propose un nativement !
    On ne jouit bien que de ce qu’on partage.

Discussions similaires

  1. comme optimiser cette requête sur 12.000 enr.
    Par chapeau_melon dans le forum WinDev
    Réponses: 2
    Dernier message: 22/03/2008, 20h36
  2. Réponses: 1
    Dernier message: 28/02/2008, 09h17
  3. Tuning requête et indexes sur fonction
    Par Mehdilis dans le forum Oracle
    Réponses: 3
    Dernier message: 26/02/2007, 14h36
  4. optimisation des requêtes sur AS400
    Par horalass dans le forum DB2
    Réponses: 2
    Dernier message: 10/08/2006, 22h22
  5. Optimisations mysql sur les requêtes SELECT: index
    Par leo'z dans le forum Débuter
    Réponses: 2
    Dernier message: 29/11/2003, 14h23

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