[SQL] Moteur de recherche, petit problème

oxman · 6 Novembre 2006

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:

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:

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 · 6 Novembre 2006

salut!
as tu créé des index?

oxman · 6 Novembre 2006

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 · 6 Novembre 2006

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 · 6 Novembre 2006

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 · 6 Novembre 2006

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 · 6 Novembre 2006

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 · 6 Novembre 2006

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 · 6 Novembre 2006

oxman a dit:
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 · 6 Novembre 2006

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 · 6 Novembre 2006

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

Code:

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 · 6 Novembre 2006

oxman a dit:
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 · 7 Novembre 2006

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:

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 · 7 Novembre 2006

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:

    (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:

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 · 7 Novembre 2006

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.

oxman · 7 Novembre 2006

Pendant que j'y suis, quel est le meilleur moyen pour mesurer le temps d'une requête ?
Car moi je n'ai pas toujours le même temps (je balance ça dans une page php avec un timer en début et en fin).

Et vu que je suis dans un travail d'optimisation, j'ai besoin d'avoir des valeurs plutôt précises, car passer de 2sec, puis après à 3.5sec ça ne le fait pas.

(Le serveur est un serveur de production, ceci explique sans doute cela).