Discussion :

[Misterweb34]

Bonsoir à tous.
J'ai un bdd de 3M d'entrée. Elle possède 4 tables :

- videos (contenant des id de thumbs)
- link_tags (contenant des id de videos et de tag)
- tags (contenant des tags)
- thumbs (contenant des id videos et adresse de thumbs)

Je crois que ça doit déjà vous dire quelques chose. Je souhaite faire une recherche de vidéos selon des tags donnés dans ma requête SQL. Voici la requête que j'exécute :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
SELECT v.*
FROM videos v 
INNER JOIN (
            SELECT lt.ID_VIDEO
            FROM link_tags lt 
               INNER JOIN videos v ON v.id = lt.ID_VIDEO
               INNER JOIN tags t ON t.ID = lt.ID_TAG 
            WHERE t.tag IN ('TAGS', 'TAGS')
) 
GROUP BY lt.ID_VIDEO HAVING Count(lt.ID_VIDEO) = *nbrTags*) vv ON v.ID = vv.ID_VIDEO

Elle me semble correcte mais lourde tout de même, ça se vérifie, une requête prend 5 secondes sur un serveur dédié Linux 64bits 4Core 2.67Ghz et 16go de ram. Le query_cache_size est à 128M.
Le problème c'est que mes capacités en mysql ne me permettent pas une optimisation plus efficace ... Vous auriez des idées ? Peut être une nouvelle organisation de la bdd ? Si oui, comment faire autrement ?

Je vous remercies par avance !

[CinePhil]

Bonsoir à tous.
J'ai un bdd de 3M d'entrée. Elle possède 4 tables :

- videos (contenant des id de thumbs)
- link_tags (contenant des id de videos et de tag)
- tags (contenant des tags)
- thumbs (contenant des id videos et adresse de thumbs)

Je crois que ça doit déjà vous dire quelques chose. Je souhaite faire une recherche de vidéos selon des tags donnés dans ma requête SQL.
Voici la requête que j'exécute :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
SELECT v.*
FROM videos v 
INNER JOIN (
            SELECT lt.ID_VIDEO
            FROM link_tags lt 
               INNER JOIN videos v ON v.id = lt.ID_VIDEO
               INNER JOIN tags t ON t.ID = lt.ID_TAG 
            WHERE t.tag IN ('TAGS', 'TAGS')
) 
GROUP BY lt.ID_VIDEO HAVING Count(lt.ID_VIDEO) = *nbrTags*) vv ON v.ID = vv.ID_VIDEO

Elle me semble correcte

Non ! Tu as mis la condition de jointure dans le HAVING !

C'est plutôt ça ta requête non ?

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT v.-- les colonnes nécessaires et pas étoile !
FROM videos v 
INNER JOIN 
(
	SELECT lt.ID_VIDEO
	FROM link_tags lt 
	INNER JOIN videos v ON v.id = lt.ID_VIDEO
	INNER JOIN tags t ON t.ID = lt.ID_TAG 
	WHERE t.tag IN ('TAGS', 'TAGS') -- 2 fois la même valeur ! Pourquoi ?
	GROUP BY lt.ID_VIDEO 
	HAVING Count(lt.ID_VIDEO) = *nbrTags*
) vv ON v.ID = vv.ID_VIDEO

mais lourde tout de même, ça se vérifie, une requête prend 5 secondes sur un serveur dédié Linux 64bits 4Core 2.67Ghz et 16go de ram. Le query_cache_size est à 128M.

Première chose à vérifier quand on constate des requêtes lentes : les index !
Ici, il faut que link_tags.ID_VIDEO et link_tags.ID_TAG soient indexés, ce qui devrait naturellement être le cas puisque ce sont des clés étrangères.

Donne nous la composition de tes tables (résultats des requêtes SHOW CREATE TABLE nom_de_la_table).

[Misterweb34]

Effectivement il y avait un soucis dans les index. Un seul index s'occupait de deux colonnes...

Il y a maintenant un index par colonne dites de "grandes utilisations". Voici mes structures de tables actuelles :

link_tags :

Code :

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

1
2
3
4
5
6
7
8
CREATE TABLE `link_tags` (
 `ID` int(11) NOT NULL AUTO_INCREMENT,
 `ID_VIDEO` int(11) NOT NULL,
 `ID_TAG` int(11) NOT NULL,
 PRIMARY KEY (`ID`),
 KEY `ID_TAG` (`ID_TAG`),
 KEY `ID_VIDEO` (`ID_VIDEO`)
) ENGINE=InnoDB AUTO_INCREMENT=3337626 DEFAULT CHARSET=latin1

tags :

Code :

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

1
2
3
4
5
6
7
	CREATE TABLE `tags` (
 `ID` int(11) NOT NULL AUTO_INCREMENT,
 `TAG` varchar(25) NOT NULL,
 `VIEW` int(11) NOT NULL,
 PRIMARY KEY (`ID`),
 KEY `TAG` (`TAG`)
) ENGINE=InnoDB AUTO_INCREMENT=100044 DEFAULT CHARSET=latin1

videos :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE `videos` (
 `ID` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
 `ID_VIDEO` mediumint(9) NOT NULL,
 `URL` text NOT NULL,
 `TITLE` text NOT NULL,
 `DURATION` int(11) NOT NULL,
 `DATE_LOCAL` bigint(20) NOT NULL,
 `DATE_VIDEO` bigint(20) NOT NULL,
 `VIDEO_FROM` int(11) NOT NULL,
 `VIEW` int(11) NOT NULL,
 PRIMARY KEY (`ID`),
 UNIQUE KEY `ID_VIDEO` (`ID_VIDEO`),
 KEY `VIDEO_FROM` (`VIDEO_FROM`)
) ENGINE=InnoDB AUTO_INCREMENT=524556 DEFAULT CHARSET=latin1

Et oui, c'est vrai, la requête que j'avais tapé n'était pas bonne. Mais celle que j’exécute correspond bien à celle que tu as fait. Je suis maintenant à 0.9s pour 3 tags, 0.5s pour deux, 0.4 pour une. C'est pas encore ça :/

Selon des personnes autours de moi, il vaudrait mieux que j'utilise les INTERSECT... ?

Edit : On vient également de me dire de posser à PostGreSql, il y a t'il un avantage certains pour ce genre d'application ?

[CinePhil]

J'ai un bdd de 3M d'entrée.

Je suis maintenant à 0.9s pour 3 tags, 0.5s pour deux, 0.4 pour une. C'est pas encore ça :/

Je trouve ça pas si mal ! Ça dépend aussi du matériel qui héberge la BDD et du logiciel qui y accède.
Tu obtiens ces temps directement en ligne de commande sur le serveur MySQL ou via un logiciel ?

Selon des personnes autours de moi, il vaudrait mieux que j'utilise les INTERSECT... ?

Sauf que ce mauvais MySQL ne connait pas INTERSECT !

Que cherches-tu à faire exactement ?