J'ai une table avec ce genre de données:
1,1,4 2,1,3 3,2,2 4,3,1 5,3,4 6,3,3
La premiere colonne c'est auto-increment, la seconde l'id d'une video et la troisieme l'id d'un tag. Je veux par exemple avoir toutes les videos qui ont le tag 4 et 3, ici j'aurai la video 1 et 3 retourné. Comment faire ca le plus efficacement possible ?

Sérieux ?
Sélect machin where truc in ()

Le 18 juin 2017 à 08:31:18 darkiron_natty a écrit :
Sérieux ?
Sélect machin where truc in ()

in m'a l'air de faire OR nn ?

Select id_video from maTable where id_tag in (3,4)

Hum... J'ai mal lu, tu veux quand c'est les deux en meme temps... Te faudrait une sous requête, je te fait ça en d'ici une petite demi heure, je suis sur mon tel là

Le 18 juin 2017 à 09:29:55 darkepsylon a écrit :
Hum... J'ai mal lu, tu veux quand c'est les deux en meme temps... Te faudrait une sous requête, je te fait ça en d'ici une petite demi heure, je suis sur mon tel là

merci khey

Bon, je répond beaucoup plus tard que prévu, désolé.

tu peux utiliser la methode de Mumbo_Jumbo.

sinon niveau syntaxe, tu peux aussi partir sur des CTE, normalement ça devrait marcher sous mysql si je dis pas de bêtise...

with sousRequete as(
					select id_video from matable where id_tag=3)

select id_video from matable 
where id_tag=4 
      and id_video in(select * from sousRequete)

Le 18 juin 2017 à 09:46:21 Mumbo_Jumbo a écrit :
Essayes ça :

select id_video from maTable where id_tag = 4 and id_video in (
select id_video from maTable where id_tag = 3
)

J'ai essayer mais ca prend trop de temps et c'est logique puisque ca cherches pour toute les videos avec le tag 3 dans la seconde requete, et y'a pas moyen de mettre un LIMIT. J'utilise des LIMIT pour ne retourner que quelques résultats a la fois pour que ca aille plus vite mais la c'est pas possible avec cette requete.
J'ai essayer de faire

SELECT t1.video_id 
FROM table t1 
INNER JOIN table t2 ON t1.video_id= t2.video_id AND t2.tag_id = 2 
INNER JOIN table t3 ON t2.video_id= t3.video_id AND t3.tag_id = 4 
WHERE t1.tag_id = 1 
LIMIT 5

La ca va plus vite mais pour plus de tags ca prend trop de temps encore.

Il faudrait voir la gueule du plan d'execution pour comprendre la performance de la requete.

Mais plusieurs questions:
pourquoi il y a un ID dans cette table ? C'est une table de jointure dont la cle primaire devrait etre le couple (videoid, tagid).
Apres, ce que tu veux faire fondamentalement c'est l'intersection de deux ensembles. J'ai pas ecrit de SQL depuis longtemps mais a la volee:

(select id_video from matable where id_tag=4) 
INTER
(select id_video from matable where id_tag=3)

et comme ca tu as une chance d'eviter la jointure.
Et si tu as un index sur id_video, tu as un algo en Theta(n) et si le SGBD est intelligent, meme un algo en une seule passe.

Fait une vue

Le 20 juin 2017 à 22:03:57 Taod a écrit :
Fait une vue

Comment tu vas faire une vue qui a du sens? les requetes vont probablement changer a chaque client.

pourquoi il y a un ID dans cette table ? C'est une table de jointure dont la cle primaire devrait etre le couple (videoid, tagid).

C'est vrai que l'id ne sert a rien et que la clé primaire aurait du etre autre chose. Tu dis que ca devrait etre le couple (videoid, tagid) mais ca ne serait pas mieux que ca soit le couple (tagid, videoid) ?
On cherche en fonction du tag donc c'est en celui ci qui est plus important en premier temps. Ensuite on trie en fonction de videoid.
En faisant la requete avec des inters est ce que c'est fait de maniere "intelligente" grace a cette clé primaire ?
Exemple je cherche videos avec un certain tag, le premier select renvoie tout ca, ensuite un deuxieme tag avec le second select. La clé primaire trie dans l'ordre d'abord tag, puis ensuite videoid, donc dans la premiere requete si la derniere ligne renvoie videoid 50 alors que dans la seconde requete la derniere ligne renvoie 55 pour l'autre tag est ce que mysql sait que ca sert a rien de chercher apres videoid 50 pour la seconde requete ?
J'imagine que oui mais je ne suis pas sur.

Et si tu as un index sur id_video, tu as un algo en Theta(n) et si le SGBD est intelligent, meme un algo en une seule passe.

Tu pourrais expliquer un peu plus ?

Merci pour votre aide.

Le 21 juin 2017 à 12:17:49 sooOkse a écrit :

pourquoi il y a un ID dans cette table ? C'est une table de jointure dont la cle primaire devrait etre le couple (videoid, tagid).

C'est vrai que l'id ne sert a rien et que la clé primaire aurait du etre autre chose. Tu dis que ca devrait etre le couple (videoid, tagid) mais ca ne serait pas mieux que ca soit le couple (tagid, videoid) ?

En plus l'auto increment consume 30% d'espace pour rien, et probablement 30% de bande passante si la table est stocke ligne par ligne.

Il n'y a pas de difference en sql entre (videoid, tagid) et (tagid, videoid). Mais peut etre que mysql utilise l'ordre pour prendre une decision interne.

[...]
J'imagine que [mysql fait un truc intelligent] oui mais je ne suis pas sur.

Bah pour savoir ca il faut regarder le plan d'execution, je ne sais pas comment voir ca dans mysql. Mais l'idee est justement qu'en mettant des cle primaire, des index, etc. on peut informer le SGBD sur le type de requete qui vont etre soumise.

Et si tu as un index sur id_video, tu as un algo en Theta(n) et si le SGBD est intelligent, meme un algo en une seule passe.

Tu pourrais expliquer un peu plus ?

• Si tu n'as pas d'index du tout, le seul algo que la base peut utiliser est essentiellement de trouver l'ensemble des id_video de tag 2, de trouver l'ensemble de id_video de tag 3. Et ensuite de matcher les deux ensemble.
  • Si le SGBD pense qu'il a assez de memoire, il peut stocker le resultat des deux requetes independement, les trier (Ca coute O(n log n)) et ensuite faire un merge (qui coute Theta(n) puisque c'est trier).
  • Si le SGBD pense qu'il n'a pas assez de memoire, il est essentiellement obliger de regarder les pairs independement (ca coute O(n^2))
• Si tu as un index sur id_video, alors tu sais que quand tu fais une passe sur la base les id_videos vont sortir trie. Donc ca veut dire qu'il te suffit de maintenir un tout petit buffer (en fait un seul element dans le cas ideal) pour stocker le flux de id_video de tag 2 parceque tu va savoir si la video est de tag 3 tout de suite apres. Donc non seulement ca te donne un algo rapide (en Theta(n)), et tu n'as besoin de lire la base qu'une seule fois; mais en plus tu n'as pas besoin d'avoir un espace de stockage intermediaire.

Bon, c'est pas opti de ouf, mais sur le jeux de données ( 20M de lignes) ça donne de très bon résultat.

il faudra surement adapter le code pour mysql par contre :

--creation d'un type listetag, celà va permettre de passer en parametre une table complète à une procédure stockée CREATE TYPE dbo.listeTag AS TABLE (id_Tag INT);GO --création de la procédure stockée alter PROCEDURE dbo.SP_GetListIdVideo (@ListTag AS dbo.listeTag READONLY) AS BEGIN --création des tables temp create table #temp(id_video int) create table #result(id_video int) create table #liste_tag(tag int) --declaration de la variable pour les tags declare @id_tag int --on insert dans la table des resultat final l'ensemble des vidéo qui ont pour tag le premier lu dans la liste donné en paramètre insert into #result select id_video from dbo.alabama where id_tag=(select top 1 * from @ListTag) --on supprime la valeur assigné precedemment de la table afin de ne pas la parcourir dans la boucle delete from #liste_tag where tag=(select top 1 * from @ListTag) --declaration d'un curseur pour boucler sur l'ensemble des tags et ouverture du curseur DECLARE liste_id_tag CURSOR FOR SELECT * from @ListTag; open liste_id_tag; --assignation du tag lu à la variable FETCH NEXT FROM liste_id_tag INTO @id_tag; WHILE @@FETCH_STATUS = 0 BEGIN --on vide la table #temp et on insére dedans les id_video qui ont pour tag celui qui est lu et qui en plus sont dans la table de resultat final delete from #temp insert into #temp select id_video from dbo.alabama where id_video in (select * from #result) and id_tag=@id_tag --on vide la table #result et on y insére les données contenue dans la table #temp qui posséde à l'heure actuel les id_video qui correspondent à tout les critére passer precedemment delete from #result insert into #result select * from #temp --on assigne la nouvelle valeur du tag FETCH NEXT FROM liste_id_tag INTO @id_tag; END --fermeture du curseur close liste_id_tag; --affichage des resultats select * from #result order by id_video end;GO --éxécution de la procédure stockée --on declare une variable du type qu'on à creer precedemment declare @listeTag as listeTag -- on lui assigne les id des tag que l'ont veut et on exécute la procédure en lui passant en paramétre la variable insert into @listeTag values (3),(4),(5),(6),(21) exec dbo.SP_GetListIdVideo @listeTag

merci de votre aide je vais essayer de comprendre tout ca et je reviendrais si (plutot quand) j'ai des questions.