Question recherche dans grosse base de données

[Steph6768]

Bonjour, j'ai un site qui à une grosse base de données de pres de 4 millions d'entrée. Je dois faire des recherches dans ses tables, mais lorsque je fais une recherche avec mon code suivant , j'ai les résultats au bout de 30 secondes, c'est énorme.

Code :

1
2
3
SELECT COUNT(*) AS compte 
FROM annonce 
WHERE titre LIKE '%chien poile long%'

Me conseillez-vous une autre fonction ou une piste afin d'optimiser ma requête ?

en vous remerciant

[overider]

Bonjour.

Pourrait on connaitre la structure et les champs indexés de la table annonce ?
Je pense qu'un index sur titre ne serait pas de trop. De plus il faut savoir qu'un recherche de type " champ like '%<mot cherche>%' est généralement trés longue même sur un champ indexe. Vous pouvez peut etre passer sur un index de type full_text et utiliser les fonctions adéquates (match).

bonne journée.

[overider]

ou encore il serait peut être plus performant de faire le traitement dans le langage de plus haut niveau (php je suppose).

Code :

 ("SELECT titre, COUNT(*) AS compte FROM annonce group by titre ")

cela va renvoyer le compte par titre.
et faire le match en php
@+

[CinePhil]

Envoyé par Steph6768

Bonjour, j'ai un site qui à une grosse base de données de pres de 4 millions d'entrée.

Ce n'est pas énorme pour un SGBD.

Je dois faire des recherches dans ses tables, mais lorsque je fais une recherche avec mon code suivant , j'ai les résultats au bout de 30 secondes, c'est énorme.

Code :

C'est déjà miraculeux qu'il trouve une réponse en cherchant des chiens à "poile" long !

Me conseillez-vous une autre fonction ou une piste afin d'optimiser ma requête ?

Comme dit par mes collègues, la recherche avec LIKE neutralise l'éventuel index classique mis sur le titre. Il peut alors être plus judicieux d'utiliser une recherche FULL TEXT.
Le défaut de ce truc est que ça ne fonctionne je crois que sur des tables utilisant le moteur MyISAM et alors adieu les contraintes d'intégrité liées aux clés étrangères qui sont ignorées par ce moteur.

[overider]

Merci pour le complément Cinephil.

J'ai trop l'habitude de ne travailler qu'avec Myisam, j'en oublie les spécificités et que les autres travaillent avec Innodb.

Bonne journée à tous.

[SQLpro]

De plus le Full Text de MySQL est plus que limité. Sur 13 fonctionnalités de FTS requise par la norme SQL il n'en fait correctement que 4 et encore sans possibilité de gérer les collations au niveau des accents.
En sus sur un fort volume de données c'est pas génial au niveau des performances et le FTS pompent beaucoup de ressources au détriment des processus transactionnels.

A lire : http://blog.developpez.com/sqlpro/p9...ext-search-no/

A +

[Steph67]

Bonjour, merci de votre réponse mais ca ne marche pas mieux, le temps de recherche est toujours aussi long, voir meme plus que avec la fonction LIKE
je ne sais plus quoi faire.

Code PHP :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<?php 
require_once('cnx/cnx.php');
 
if(isset($_GET['go'])){
 
 
    echo date('H-i-s');
 
    $query_total = mysql_query("SELECT id FROM annonce WHERE MATCH (titre) AGAINST ('chien poil long)") or die(mysql_error());
 
    $total_query = mysql_num_rows($query_total);
    echo '<br><br>';
    echo date('H-i-s');
    echo '<br><br>';
    echo 'Total  : '.$total_query.'<br /><br />';
}
?>

[patic]

Bonjour,
Voilà un sujet qui m'intéresse bien.
Avis au Pro, est ce que passer par la création de son propre index, qui serait un simple table à deux colonnes, l'une avec l'id et l'autre avec chaque mot décomposé du titre (éventuellement en filtrant sur une liste de mots à exclure "le", "la", "de", etc), le tout avec un index sur la colonne titre, pourrait être une bonne solution ?
Lors de la recherche, on décompose la chaine en mots uniques qu'on recherche dans cette seconde table et on récupère les résultats qui remplissent toutes les conditions (chaque mot) ?
Cela permettrait de ne plus faire de LIKE avec les %.
Qu'en pensez vous ?

[JeitEmgie]

Sans avoir besoin de rentrer dans les défauts de MySQL concernant le FTS, on remarquera que le pattern recherché commence par une wild card ('%chien poile long%') , ce qui ne donne jamais de bonnes performances quel que soit le RDBMS…

[SQLpro]

Ce n'est pas tout à fait vrai. Par exemple pour Intellixir sous MS SQL Server, j'ai mis au point des index rotatifs dans le moteur d'indexation textuel que nous avons développé et la recherche est instantanéee même pour le LIKE %toto% sur de très grand volumes de données (text mining)

A +.

[JeitEmgie]

Envoyé par SQLpro

Ce n'est pas tout à fait vrai. Par exemple pour Intellixir sous MS SQL Server, j'ai mis au point des index rotatifs dans le moteur d'indexation textuel que nous avons développé et la recherche est instantanéee même pour le LIKE %toto% sur de très grand volumes de données (text mining)

A +.

exception qui confirme bien la règle… dans le cas général, la wild card en tête est une tueuse de perf… sinon vous n'auriez pas eu besoin de faire du custom…

[SQLpro]

Sur le '%toto' un index sur fonction ou sur colonne calculée suffit !
Malheureusement c'est impossible sous MySQL....

A +

[-kiki-]

Envoyé par Steph67

Bonjour, merci de votre réponse mais ca ne marche pas mieux, le temps de recherche est toujours aussi long, voir meme plus que avec la fonction LIKE
je ne sais plus quoi faire.

Code PHP :

Salut,

Dans votre nouvel exemple, je ne crois pas que votre problème de perfs vienne de l'index Full Text :
1- Vous n'avez pas fermé la quote dans ton AGAINST (et avec mysql une requête contenant erreur peut prendre beaucoup de temps! bien plus que si elle n'avait pas d'erreur!)
2- Vous récupérez tous les id au lieu de récupérer le count! c'est normal?

L'index FullText de Mysql n'est effectivement pas au top, mais sur 4M lignes ça devrait prendre moins d'1sec! (et je compte large, ce serait déjà très lent!)

Sinon comme dit patic, vous pouvez faire votre propre moteur de recherche. SqlPro a fait un article assez clair sur le sujet.

@Sqlpro : comment avez-vous fait pour qu'un LIKE '%..%' utilise vos propres indexs??