Discussion :

[dynexd]

Bonjour,
j'ai un site avec une table qui compte 200 000 entrées, j'utilise un cron job, qui, toutes les 5 minutes, controlent ces entrées, j'aimerai savoir comment les requetes SQL procedent quand il y a plusieurs WHERE pour pouvoir optimiser mes requetes.

Je recupère des infos depuis plusieurs centaines de flux RSS, je recupère les infos de ces news, si elles sont déjà présentes dans ma base de donnée, je ne les recupere pas, et je stocke les news pas présentes dans ma base..

Pour chaque news de chaque flux rss je la compare donc pour voir si elle est présente dans ma base, un gros travail vu qu'il y a plus de 200 000 entrées.

J'utilise l'adresse url du lien vers la news ou la date pour comparer et vérifier l'existence d'une news, j'execute donc une requete pour chaque news de chaque flux, c'est cette requete que je souhaite optimiser...

id_feed correspond à l'id unique d'un flux rss, comme ça la requete ne chercherait l'url du lien parmi uniquement les news ayant le bon id de flux..

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Si je met dans ma requête SELECT * WHERE url_news="le_lien_vers_lanews.php" AND id_feed=5"

Est ce que MySQL va filtrer les champs qui ont id_feed=5 et chercher uniquement parmi eux si url_news est bien égal à "le_lien_vers_lanews.php", ou alors est ce qu'il va chercher parmi les 200 000 champs la valeur texte de url_news PUIS l'id_feed ???

J'aimerai bien comprendre comment dire dans une requete de trier d'abord avec id_feed et ensuite verifier l'url_news et pas l'inverse

Merci beaucoup

[Invité]

l'ordre des critères dans la clause where est important


présentement l'engin fera un scan pour 'le_lien....' et ensuite utiliser ton indexes pour le id_feed=5 (si tu as un indexes)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT * WHERE url_news="le_lien_vers_lanews.php" AND id_feed=5"

tu dois les inverser

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT * WHERE id_feed=5 and url_news="le_lien_vers_lanews.php"

si tu as MYSQL5 tu peux aussi partionner une table.

En gros cela te permet de faire une grosse table virtuelle mais en dessous c'est plusieurs tables plus petites qui sont créés

http://dev.mysql.com/doc/refman/5.1/...ing-range.html

tu peux peux regrouper les feeds les plus gros dans des partitions isolées. À part la définition de la table, le 'partitionning' ne change en rien la manière que tu te sers pour accéder les données.

Un bonne préparation améliorera les performances.

Si tu veux que cela soit plus rapide, tu pourrais insérer une colonne HASH dans ta table de feed qui correspond au CRC32 ou SHA5 de l'url. Tu mets l'indexe sur cette colonne au lieu d'indexer l'url (qui peut contenir plus de 200 caractères).


De cette facon, quand tu lis un feed, tu calcules la valeur du hash dans php, ensuite tu demandes a mysql si tu as deja une news avec le meme hash. Le hash ayant un maximum d'environ 40 caracteres, sera surement plus rapide que de rechercher un URL.

Tu ne déclares pas le hash comme un varchar mais plutot un CHAR32 ou char40..

c'est possible d'optimiser plus encore, je te laisse réfléchir sur ce que tu veux ou a besoin d'abord.

[dynexd]

l'ordre des critères dans la clause where est important

Merci pour l'info
Très important, j'avais encore lu ça nul part sur le net ^^ Ca m'avance déjà pas mal

La partition de table ca m'a l'air interessant pour la suite Une partition par langue par exemple histoire d'optimiser

tu me conseilles crc32 ou sha pour encrypter les url ??

En fait je vais utiliser plusieurs techniques pour identifier les flux, soit l'url, soit la date, soit un id récupéré dans l'url..

"Indexes" c'est l'option Index pour les champs dans phpMyAdmin ? c'est grave si quasi tous mes champs dans ma table news sont des index ? ( des id vers d'autres tables ) , la date et le lien_url(ou plutot le hash) je dois les mettre en index aussi ?

Et encore merci pour ta réponse

[Invité]

La partition de table ca m'a l'air interessant pour la suite Une partition par langue par exemple histoire d'optimiser

Effectivement, si tu sa genre 2 langues, ca t'assures de démêler physiquement tes news. C'est effectivement inutile de chercher une news francaise dans une liste de news anglaise. La beauté du partitionning c'est que les requêtes ne changent pas. Seulement la définition initiale de la table.

On appelle ça une repartition verticale de tes données, pour améliorer d'avantages, tu peux faire une partition horizontale de tes données. Puis-je breveter le terme partition diagonale?

tu me conseilles crc32 ou sha pour encrypter les url ??

md5=32bits, sha1=40bits, j'irais donc avec sha1 pour minimiser le nombre de collisions possibles (2 chaines avec une valeur de HASHing identique)

En fait je vais utiliser plusieurs techniques pour identifier les flux, soit l'url, soit la date, soit un id récupéré dans l'url..

bien tu peux utiliser le nombre de critère que tu le désires, tu exécutes ensuite la fonction de hashing sur l'ensemble de ces données..

donc cela ne te fait qu'un seul champ à chercher! :-)

petite suggestion

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
 
 
 //je lis mes feeds ici
 
 //ici on met une boucle pour traiter chaque flux
 $info=array();
 $info['id_feed']=5;
 $info['url']='http://www.lucraymond.net';
 $info['langue']='fr'
 $hash= sha1(serialize($info));
 
 //ici on valide si hash existe dans la BD
 //ici on insère si hash existe pas

"Indexes" c'est l'option Index pour les champs dans phpMyAdmin ? c'est grave si quasi tous mes champs dans ma table news sont des index ? ( des id vers d'autres tables ) , la date et le lien_url(ou plutot le hash) je dois les mettre en index aussi ?
Et encore merci pour ta réponse

tu dois mettre un index sur seulement les champs dont tu désires faires des recherches. Si tu ne recherche jamais par date, tu n'en met pas.

n'oublie pas que lorsque tu insere des nouvelles données dans la table, il y a une étape d'insertion des données et ensuite c'est la mise à jour des indexes. Donc si tu as 10 indexes, en insérant un nouvel enregistrement tu te retrouves à provoquer 11 actions à ton engin de base de données.

Sans compter l'espace disque perdu pour maintenir les indexes.

meme si tu as des indexes, cela ne veut pas dire que mysql les utilisent

si tu met un indexe sur la colonne URL et que tu cherches:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
... where URL like '%luc%';   // n'utilisera pas l'index
... where URL like '%luc';   // n'utilisera pas l'index, mais une solution existe
... where URL like 'luc%';   // utilisera l'index

[Invité]

si tu veux améliorer genre (100x et +) ton processus, je te conseille d'améliorer ta technique! :-)

quand tu lis tes feeds, tu vas calculer tes hash pour ensuite voir un par un si ils existent dejà dans ta base de données?

ce que tu peux faire (tout dépendant de ton volume de données) c'est de mettre en mémoire tes dernières nouvelles que tu as ajoutées dans ta table

pseudocode

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
 
 
 
  $feeds=AllonsChercherlesNewsSurLeWeb(...);  //retourne un tableau de FEEDS
 
  $hashes=CalculeHash($feeds); retourne un tableau de hash correspondant a nos nouvelles
 
  $dernierhashes= Mysql_query('select hash from mes_news order by DATEdeLaNews desc limit 0,1000');
 
 // valide si chaque les HASH dans HASHES 
 // si oui, on ajoute pas...
 // si non, tu peux valider dans la base de donner avec un SELECT pour etre sur qu'il nest pas présent, ensuite tu ajoutes..

tu peux changer 1000 pour 10000..

si tu indexes les news assez régulièrement (genre 30 minutes), en le mettant a 5000, si tu lis 3000 news par 30 minutes, tu te retrouveras a faire une requete de base + 1 pour chaque nouvelle news au lieu de faire 2 requêtes pour chaques news que tu lis.


si tu lit aux 30 minutes : 100 feeds avec 2000 news news et que seulement 40 sont nouvelles. Tu fais 2000 requetes de validation d'existences et 40 insertions..

avec ma technique:
41 requetes de validation (1+40) d'existences + 40 insertions.

tu m'en reparleras! j'ai utilisé plusieurs fois.

[dynexd]

n'oublie pas que lorsque tu insere des nouvelles données dans la table, il y a une étape d'insertion des données et ensuite c'est la mise à jour des indexes. Donc si tu as 10 indexes, en insérant un nouvel enregistrement tu te retrouves à provoquer 11 actions à ton engin de base de données.

Oui donc je dois mettre mon champs HASH en index alors !

Mais est ce que je dois mettre en index les champs ? ( j'en ai 4 ou 5 ) correspond à des id pointant vers d'autres tables ( qui correspondent à des categories, chaque flux est associé à 4 ou 5 tables de categories ) qui me permettent d'organiser ces news.. je ne comprends pas comment je peux vérifier l'existence de 1000 news avec seulement 40 requete d'existence

Sinon concernant ton deuxième post je suis un peu dans le flou Pour info je lance un cronjob qui index toutes les 5 minutes..

De plus le LIMIT 1000 ou 10 000, je ne suis pas sur que ce soit adapté, parmi les flux que j'index certains sont beaucoup moins à jours que d'autres, et peuvent avoir des news qui ont plusieurs mois, je peux avoir indexé bien plus de 10 000 news plus récentes, et ce flux avoir toujours un vielle news de stockée.....

Merci encore pour tes précisions qui me sont très utiles !!


Une question que je me pose aussi (j'avoue profiter de tes connaissances très poussées j'en profite tant que je peux ) : j'aimerai créer des laisons supplémentaires entre les news et catégories présentes sur le site, par exemple le feed "Travauxfacile.com" est rangé dans la categorie "Travaux" donc les news prises sur ce feed seront dans la categorie Travaux

1. Concernant ma table News qui stocke toutes les news, est ce qu'il vaut mieux créer un champs Categorie avec l'id vers la table Catégories ou est ce que le simple champs id_feed correspond à la table Feeds suffit, ou je ferait alors un JOIN feeds ( categorie ) ON feeds.id = news.id_feed ???

2. Si je souhaite créer des liaisons supplémentaires, par exemple associer des mots à des categories que je définirai dans ma base, de manière a ce que si une news paru sur travauxfacile.com comprends le mot "Sarkozy" si ce mot est stocké dans ma base et indique que la news doit aussi apparaitre dans la categorie politique, comment puis je créer ces liaisons ? Est ce mieux de rajouter un champs 'Liaisons' dans ma table 'news' ou alors est ce que je crée carrément une table 'liaison" : id, id_categorie, id_news ?