[Optimisation] SELECT multi-tables avec INDEX ou FULLTEXT

Bonjour,

Je viens vers vous pour une optimisation de mon schéma de bdd parce que je pense pas que seule la requête soit la cause de la lenteur.

Voici le schéma actuel :

Code:

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
 
--
-- Structure de la table `produit` ~70.000 lignes
--
 
CREATE TABLE IF NOT EXISTS `produit` (
  `id_produit` int(11) NOT NULL AUTO_INCREMENT,
  `nom_fabricant` varchar(32) NOT NULL,
  `code_fabricant` varchar(20) NOT NULL,
  `sku` varchar(12) NOT NULL,
  `desc1` varchar(35) NOT NULL,
  `desc2` varchar(35) NOT NULL,
  `ref_fabricant` varchar(20) NOT NULL,
  `code_barre` varchar(13) NOT NULL,
  `cat_produit` varchar(4) NOT NULL,
  `ft_imported` tinyint(1) NOT NULL,
  PRIMARY KEY (`id_produit`),
  KEY `ft_produit` (`nom_fabricant`,`code_fabricant`,`desc1`,`desc2`,`ref_fabricant`,`code_barre`,`ft_imported`),
  FULLTEXT KEY `index_produit` (`nom_fabricant`,`code_fabricant`,`desc1`,`desc2`,`ref_fabricant`,`code_barre`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;
 
-- --------------------------------------------------------
 
--
-- Structure de la table `stock_fr` ~78.000 lignes
--
 
CREATE TABLE IF NOT EXISTS `stock_fr` (
  `stock_sku` varchar(12) NOT NULL,
  `stock_ref_fabricant` varchar(20) NOT NULL,
  `stock_qte_dispo` smallint(6) NOT NULL,
  `stock_qte_cmd` smallint(6) NOT NULL,
  `etat` varchar(8) NOT NULL,
  PRIMARY KEY (`stock_sku`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
-- --------------------------------------------------------
 
--
-- Structure de la table `stock_eu` ~30.000 lignes
--
 
CREATE TABLE IF NOT EXISTS `stock_eu` (
  `stock_eu_sku` varchar(12) NOT NULL,
  `stock_eu_ref_fabricant` varchar(20) NOT NULL,
  `stock_eu_qte_dispo` varchar(8) NOT NULL,
  `stock_eu_qte_cmd` varchar(8) NOT NULL,
  `stock_eu_etat` varchar(8) NOT NULL,
  PRIMARY KEY (`stock_eu_sku`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
-- --------------------------------------------------------
 
--
-- Structure de la table `xml_valeur` ~390.000 lignes
--
 
CREATE TABLE IF NOT EXISTS `xml_valeur` (
  `sku` varchar(12) NOT NULL,
  `id_xml_propriete` varchar(20) NOT NULL,
  `libel_xml_valeur` varchar(255) NOT NULL,
  PRIMARY KEY (`sku`,`id_xml_propriete`),
  KEY `libel_xml_valeur` (`libel_xml_valeur`),
  FULLTEXT KEY `libel_xml_valeur_2` (`libel_xml_valeur`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Un produit possède une ou plusieurs valeurs de `priopriétés` (table non représentée car non utilisée et utile dans ce cas-ci).

Là où commence les problèmes c'est pour les recherches par mot-clés.

J'utilise cette requête. Elle retourne ~900 lignes en ~15-20 secondes (affichage compris) et c'est là que c'est gênant. C'est beaucoup trop long.

Code:

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
    P.desc1, P.sku, P.desc2, P.cat_produit, nom_fabricant, stock_qte_dispo, stock_qte_cmd, etat, stock_eu_qte_dispo, stock_eu_qte_cmd, stock_eu_etat, id_produit, ref_fabricant, code_barre, code_fabricant, libel_xml_valeur
FROM produit P
    LEFT OUTER JOIN stock_fr TF ON P.sku = TF.stock_sku
    LEFT OUTER JOIN stock_eu TR ON P.sku = TR.stock_eu_sku
    INNER JOIN xml_valeur XV ON XV.sku = P.sku
WHERE (
        desc1 LIKE '%windows%'
        OR desc2 LIKE '%windows%'
        OR nom_fabricant LIKE '%windows%'
        OR code_fabricant LIKE '%windows%'
        OR ref_fabricant LIKE '%windows%'
        OR code_barre LIKE '%windows%'
        OR libel_xml_valeur LIKE '%windows%'
    ) AND (
        desc1 LIKE '%premium%'
        OR desc2 LIKE '%premium%'
        OR nom_fabricant LIKE '%premium%'
        OR code_fabricant LIKE '%premium%'
        OR ref_fabricant LIKE '%premium%'
        OR code_barre LIKE '%premium%'
        OR libel_xml_valeur LIKE '%premium%'
    )
GROUP BY P.id_produit

Je suis passé par une recherche de type FULLTEXT mais c'est aux alentours de 32 secondes cette fois, avec cette requête :

Code:

1
2
3
4
5
6
7
8
9
10
 
SELECT P.sku, P.desc1, P.desc2, P.cat_produit, prix_public, prix_revendeur, nom_fabricant, produit_en_stock, stock_qte_dispo, stock_qte_cmd, etat, stock_eu_qte_dispo, stock_eu_qte_cmd, stock_eu_etat, id_produit, content, ref_fabricant, code_barre, code_fabricant, libel_xml_valeur
FROM price P
    LEFT OUTER JOIN totfrhrl TF ON P.sku = TF.stock_sku
    LEFT OUTER JOIN totrihrl TR ON P.sku = TR.stock_eu_sku
    INNER JOIN xml_valeur XV ON XV.sku = P.sku
WHERE
    MATCH (`desc1`, `desc2`, `nom_fabricant`, `code_fabricant`, `ref_fabricant`, `code_barre`, `libel_xml_valeur`)
    AGAINST ('+windows +premium' IN BOOLEAN MODE)
GROUP BY P.id_produit;

J'avoue ne plus avoir d'idée pour que les temps de recherche soit acceptables. A condition qu'ils puissent l'être mais j'en doute.

Auriez-vous une idée pour que j'optimise au mieux cette recherche, que ce soit au niveau conceptuel que SQL ?

Bonjour

J'ai le vague sentiment que Mysql n'utilise pas tes index FT.
Quand tu fais une requete en mode Booléen, Mysql est capable de parcourir tes lignes en séquentiel sans utiliser les index si il ne trouve pas l'index qui lui faut.

Je ne pense pas que tu puisses associer dans une seule requête FT des colonnes provenant de deux tables.

As tu essayé de mettre les colonnes dans le même ordre que celles de la création de ton index en retirant la septième colonne provenant de la seconde table ?

salut,

déjà un index multiple comme `ft_produit`est inutile et contre productif...

ensuite, un index simple n'est utilisé que si et seulement aucun clause or ne joue sur lui

soit un index sur a

Code:

... where a=5

index utilisé

Code:

... where a=5 or b=4

index non utilisé

Code:

... where a=5 and b=2

index utilisé

une clé primaire non numérique n'est absolument pas optimisé...

je suis pas sur que ton modèle de données soit bien pensé...:aie:

Bonjour,

J'ai bien conscience qu'une clé primaire non numérique n'est pas l'idéal, mais je suis tributaire des spécifications de mon fournisseur.

Mais je vais faire autrement, en transformant la table `xml_valeur` en table de mot-clés dans laquelle je vais taper.
Le souci était qu'il est possible qu'un produit ne dispose pas de fiche technique donc pas de valeurs pour cette fameuse table.

Mais je vais les générer depuis la courte description dont je dispose et ça fonctionnera ainsi.

Il est clair qu'en ajoutant l'identifiant produit (que j'ai rajouté dans la table `produit`) en clé primaire dans la table `xml_valeur` (en gardant le sku pour les mises à jour), la recherche avec LIKE (AND) fonctionne à merveille et les temps de recherche sont parfait.

Je reviens vers vous quand j'ai avancé et je mettrai mes changements pour avoir votre avis également ;)

Merci bien en tout cas pour le temps passé sur ce sujet.

Chose promise, chose due !

J'ai finalement intégré toutes les données susceptibles de faire l'objet d'une recherche dans la table `xml_valeur` et je fais une recherche avec LIKE. Les perfs sont excellentes avec les INDEX bien placés.

Sujet clos. Merci.