[SQL] Moteur de recherche, petit problème

[oxman]

Salut,

J'ai fait un moteur de recherche pour mon site, à l'époque il marchait plutôt bien (moins de 5 secs pour retourner les résultats), mais depuis la BDD a grossis, et le script depuis un ajout conséquent dans la BDD est complètement à la ramasse. 15 secs de recherche minimum.

J'ai donc décidé de tout refaire mes requêtes, mais je rencontre un problème bizarre que je vais vous exposer ici.

Tout d'abord la structure de mes tables :

Code: Tout sélectionner

CREATE TABLE `structure` (
  `STRUCT_ID` int(11) NOT NULL auto_increment,
  `VILLE_ID` int(11) NOT NULL default '0',
  `STRUCT_NOM` varchar(80) NOT NULL default '',
  `STRUCT_DESC` text NOT NULL,
  PRIMARY KEY  (`STRUCT_ID`),
  KEY `STRUCT_NOM` (`STRUCT_NOM`),
  KEY `VILLE_ID` (`VILLE_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `apourss_sscat` (
  `SS_SSCAT_ID` smallint(3) unsigned NOT NULL default '0',
  `STRUCT_ID` int(11) NOT NULL default '0',
  UNIQUE KEY `SS_SSCAT_ID_2` (`SS_SSCAT_ID`,`STRUCT_ID`),
  KEY `STRUCT_ID` (`STRUCT_ID`),
  KEY `SS_SSCAT_ID` (`SS_SSCAT_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `ss_sscategorie` (
  `SS_SSCAT_ID` smallint(3) unsigned NOT NULL default '0',
  `SS_SSCAT_LIB` varchar(60) NOT NULL default '',
  `SSCAT_ID` int(11) NOT NULL default '0',
  `THEMA_ID` int(11) NOT NULL default '1',
  PRIMARY KEY  (`SS_SSCAT_ID`),
  KEY `SSCAT_ID` (`SSCAT_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

La table structure a 63000 entrées.
La table apourss_sscat avait 63000 entrées, elle est récemment passée à 105000 entrées.
La table ss_sscategorie a 21 entrées.


Voici la partie de mon moteur qui me pose problème :

Code: Tout sélectionner

select s1.struct_id as id
        from structure as s1 use index (primary)
        inner join ville as v1 on (v1.ville_id = s1.ville_id)
        inner join apourss_sscat as ap1 on (ap1.struct_id = s1.struct_id)
        inner join ss_sscategorie as ss1 on (ss1.ss_sscat_id = ap1.ss_sscat_id)
        where
            (
                struct_nom like '%$recherche%' or
                ville_nom like '%$recherche%' or
                ville_cp like '%$recherche%' or
                struct_desc like '%$recherche%'
            )

Tout semble normal, sauf que..
Si je vire la partie :
inner join apourss_sscat as ap1 on (ap1.struct_id = s1.struct_id)
inner join ss_sscategorie as ss1 on (ss1.ss_sscat_id = ap1.ss_sscat_id)

Ma requête prend 1.5 sec environs, donc génial.
Si je rajoute cette partie elle prend plus de 4.5 sec !

Je trouve que c'est énorme le temps en plus par rapport à ce que font les lignes.

J'ai effectuer une "défragmentation" (option phpmyadmin) de chacune de ces trois tables. Mes index me semblent ok.
Bref, je ne vois pas ce qui justifie la durée de cette requête.

Vous pouvez m'éclairer svp ?

Merci

[tangui]

salut!
as tu créé des index?

[oxman]

Salut,

Merci de prendre le temps de répondre.
Tu as du lire mon post un petit peu rapidement, tu n'as pas vu ça par exemple :
KEY `STRUCT_ID` (`STRUCT_ID`),

Tous les "key kkchose" dans ma définition de table, veux dire que j'ai mis un index sur le nom de la colonne.

[mf]

euh passe a mysql 4 si t'y est pas et vire les jointure c'est ca qui fait rammé et utilise les select dans les where

[oxman]

Je suis sur mySQL 4. D'utiliser des FROM et WHERE au lieu de INNER JOIN ne change rien à mon problème (enfin je gagne peut-être 300ms).

[Serious]

La premiere chose que je ferais, c'est de passer en full text. Tu crees un champ TEXT supplementaire contenant la valeur des ville_nom, ville_cp, struct_nom et struct_desc concatenes. Puis tu utilises MATCH pour la recherche. Ca devrait diviser par 20 ton temps de recherche. Pour les inners, je les supprimerais et je ferais ensuite une suite de requetes simples pour obtenir l'information pour chaque ligne. Ou si tu as un nombre limite de resultats, une seule requete avec un IN sur les ids.

[mf]

euh pourquoi tu fait des jointure sur tes tables categories si tu cherche rien dedans....ou j'ai loupé un épisode dans ta structure...
là si j'ai bien compris tu cherche des donnés uniquement dans ta table "ville" et "structure"

vite fait je ferra ca a la place
select struct_id from structure
where
ville_id =(select ville_id from ville where ville_nom like '%$recherche%' or ville_cp like '%$recherche%' )
or
struct_nom like '%$recherche%'
or
struct_desc like '%$recherche%'

[oxman]

Serious, je suis en InnoDB, je peux pas utiliser de FULLTEXT.

mf, ça n'est qu'un bout de ma requête pour localiser le problème, j'utilise sscat_id pour regrouper la recherche de mes résultats, j'effectue aussi une recherche dans ss_sscat_lib.

[mf]

Serious, je suis en InnoDB, je peux pas utiliser de FULLTEXT.

mf, ça n'est qu'un bout de ma requête pour localiser le problème, j'utilise sscat_id pour regrouper la recherche de mes résultats, j'effectue aussi une recherche dans ss_sscat_lib.

ok alors je complete

select struct_id from structure
where
(
ville_id =(select ville_id from ville where ville_nom like '%$recherche%' or ville_cp like '%$recherche%' )
or
struct_nom like '%$recherche%'
or
struct_desc like '%$recherche%' )

and struct_id = (select struct_id from apourss_sscat where (ce que tu veu) and ss_sscat_id=(select ss_sscat_id from ss_sscategorie where (ce que tu veu aussi) ) ) )

[oxman]

Alors là je dis MR mf.

Je ne connaissais pas du tout cette façon de procéder en balançant plein de sous-requêtes "partout", ça me semblait bien plus sale, et donc moins rapide. Mais c'est incroyablement plus rapide. Ma requête passe même à 1 sec.

J'ai donc une excellente base pour travailler (ce système de sous-requêtes).
Merci beaucoup mf =)

Pour la petite histoire, ce problème est dans le cadre de mon travail, donc là j'ai effectué des tests à la maison, je mettrais vraiment en application tout ça demain, je vous tiens au courant.

[oxman]

Je crois que je capte vite le principe (si je ne commet pas d'erreur) :

Code: Tout sélectionner

select s1.struct_id as id
        from structure as s1 use index (primary)
        where
        (
                ville_id in (select ville_id from ville where ville_nom like '%$recherche%' or ville_cp like '%$recherche%') or
                struct_nom like '%$recherche%' or
                struct_adr like '%$recherche%' or
                struct_comp_adr like '%$recherche%' or
                struct_tel like '%$recherche%' or
                struct_fax like '%$recherche%' or
                struct_email like '%$recherche%' or
                struct_site_web like '%$recherche%' or
                struct_desc like '%$recherche%' or
                NULL not in (select 1 from prest where prest_id in (select prest_id from propose where propose.struct_id in (select struct_id from structure)) and prest_lib like '%$recherche%')
            )
            and struct_id in (select struct_id from apourss_sscat where ss_sscat_id in (select ss_sscat_id from ss_sscategorie))


C'est un test à l'arrache pour inclure un LEFT JOIN de la table prest (structure -> propose -> prest)

[mf]

Alors là je dis MR mf.

Je ne connaissais pas du tout cette façon de procéder en balançant plein de sous-requêtes "partout", ça me semblait bien plus sale, et donc moins rapide. Mais c'est incroyablement plus rapide. Ma requête passe même à 1 sec.

J'ai donc une excellente base pour travailler (ce système de sous-requêtes).
Merci beaucoup mf =)

c'est pas salle justement oracle permettais ca depuis longtemps et grace a mysql4 on peu aussi le faire maintenant en faite faire des jointure multipli le nombre de donné travaillé alors que avec les select dans les close where on reduit par branche le nombre d'élément et donc bien plus rapide

[oxman]

Hum, après plus de tests, la solution visiblement ne va pas me convenir.

Pourquoi ?
Je ne peux pas utiliser AND pour struct_id in blabla, mais OR, ce qui donne :

Code: Tout sélectionner

select s1.struct_id as id
        from structure as s1 use index (primary)
        where
        (
                ville_id in (select ville_id from ville where ville_nom like '%$recherche%' or ville_cp like '%$recherche%') or
                struct_nom like '%$recherche%' or
                struct_adr like '%$recherche%' or
                struct_comp_adr like '%$recherche%' or
                struct_tel like '%$recherche%' or
                struct_fax like '%$recherche%' or
                struct_email like '%$recherche%' or
                struct_site_web like '%$recherche%' or
                struct_desc like '%$recherche%' or
                struct_id in (select struct_id from apourss_sscat where ss_sscat_id in (select ss_sscat_id from ss_sscategorie where ss_sscat_lib like '%$recherche%')) or
                NULL not in (select 1 from prest where prest_id in (select prest_id from propose where propose.struct_id in (select struct_id from structure)) and prest_lib like '%$recherche%')
            )

Je gagne 500ms selon les cas avec cette méthode par rapport à avant, mais ça ne reste pas génial du tout. Sans compter que je ne vois pas comment récupérer la valeur de la colonne sscat_id qui se trouve dans la table ss_sscategorie. (De même que ss_sscat_lib qui se trouve dans ss_sscategorie).

[oxman]

J'essaye de prendre le problème à l'envers.
C'est à dire de récupérer tous les ID des structures qui m'intéresses, avec un truc du genre :

Code: Tout sélectionner

    (select s1.struct_id as id
       from structure as s1 use index (primary)
   where
   (
      ville_id in (select ville_id from ville where ville_nom like '%$recherche%' or ville_cp like '%$recherche%') or
                struct_nom like '%$recherche%' or
                struct_desc like '%$recherche%'
   )
   ) UNION (
   select struct_id as id from apourss_sscat where ss_sscat_id in (select ss_sscat_id from ss_sscategorie where ss_sscat_lib like '%$recherche%')
   ) UNION (
   select struct_id as id from propose where propose.prest_id in (select prest_id from prest where prest_lib like '%$recherche%')
        )";

Puis je voulais prendre tous les ID des sous cat assignés aux struct_id que je récupère.

Mais visiblement MySQL n'aime pas les sous requêtes quand il y a des UNION dedans.

Je prends pour exemple ce test simple qui bug :

Code: Tout sélectionner

SELECT *
FROM langue
WHERE langue_id
IN (
(

SELECT langue_id
FROM langue
WHERE langue_id < 10
) UNION (
SELECT langue_id
FROM langue
WHERE langue_id > 10
)
)


Quelqu'un a donc une solution ?
Car je trouve toujours que mon problème est bizarre.

[oxman]

En fait sans () pour les UNION ça marche.
Mais ça ne résout pas mon problème, la requête est aussi lente qu'avant.