Discussion :

[lr]

Salut,

Je dois permettre à un utilisateur de chercher des articles qui sont liés à certains tags qu'il peut choisir pour filter.

Et il doit aussi pouvoir entrer des mot-clés pour une recherche full text.

Et il peut définir des groupes de filtres afin de gérer des AND et OR. Au sein d'un groupe les critères sont OR tandis que entre les groupes c'est AND. Ca pourrait donner quelque chose comme ça :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
filter_group1 : (
  (tag1(123) OR tag2(234))
  OR
  (keyword1 OR keyword2)
)
AND
filter_group2 : (
  (tag3(345))
  OR
  (keyword3 OR keyword4)
)

L'association entre les articles et les tags se fait avec une table du genre term_node( tag_id, article_id ).

La table article a un champ title et un champ body sur lesquels j'ai créé un index FULLTEXT(title, body).

Il est susceptible d'y avoir entre 1 et 6 groupes de filtres. Et il peut exister 1 million d'articles.

La requête est construite en partie dynamiquement en PHP et ressemble à ça :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT
	node.nid AS article_id,
	node.title AS article_title,
	node.body AS article_body
FROM
	node
	INNER JOIN term_node tn1 ON tn1.nid = node.nid
	INNER JOIN term_node tn2 ON tn2.nid = node.nid
WHERE
	(
		(tn1.tid IN (123, 234))
		OR
		(MATCH(node.title, node.body) AGAINST ('keyword1 keyword2' IN BOOLEAN MODE))
	)
	AND (
		(tn2.tid IN (345))
		OR
		(MATCH(node.title, node.body) AGAINST ('keyword3 keyword4' IN BOOLEAN MODE))
	)

Est-ce que de telles requêtes ont une chance d'être suffisamment performantes vu la taille du dataset et les jointures ? Sachant que je vais aussi devoir trier ?

Merci d'avance

[CinePhil]

Si les nid et tid sont tous indexés, ça ne devrait pas poser de problème.

[lr]

Si les nid et tid sont tous indexés, ça ne devrait pas poser de problème.

Merci. J'ai vérifié, ils sont indexés mais ça prend quand même plus de 80 secondes à exécuter la requête alors que je n'ai que 40'000 lignes dans la table node et que l'écrasante majorité des body fait moins de 10'000 signes (il y en a quatre qui font entre 100'000 et 400'000).

[CinePhil]

On peut avoir la structure exacte des tables ?

[lr]

On peut avoir la structure exacte des tables ?

J'avais essayé de simplifier pour rester au niveau du concept, mais oui, bien volontiers.

Petite complexité, il y a en fait deux tables : node qui est la table principale et node_revisions qui contient les différentes versions de title et body d'un node.

Code :

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

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
CREATE TABLE `node` (
  `nid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `vid` int(10) unsigned NOT NULL DEFAULT '0',
  `type` varchar(32) NOT NULL DEFAULT '',
  `language` varchar(12) NOT NULL DEFAULT '',
  `title` varchar(255) NOT NULL DEFAULT '',
  `uid` int(11) NOT NULL DEFAULT '0',
  `status` int(11) NOT NULL DEFAULT '1',
  `created` int(11) NOT NULL DEFAULT '0',
  `changed` int(11) NOT NULL DEFAULT '0',
  `comment` int(11) NOT NULL DEFAULT '0',
  `promote` int(11) NOT NULL DEFAULT '0',
  `moderate` int(11) NOT NULL DEFAULT '0',
  `sticky` int(11) NOT NULL DEFAULT '0',
  `tnid` int(10) unsigned NOT NULL DEFAULT '0',
  `translate` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`nid`),
  UNIQUE KEY `vid` (`vid`),
  KEY `node_changed` (`changed`),
  KEY `node_created` (`created`),
  KEY `node_moderate` (`moderate`),
  KEY `node_promote_status` (`promote`,`status`),
  KEY `node_status_type` (`status`,`type`,`nid`),
  KEY `node_title_type` (`title`,`type`(4)),
  KEY `node_type` (`type`(4)),
  KEY `uid` (`uid`),
  KEY `tnid` (`tnid`),
  KEY `translate` (`translate`)
) ENGINE=MyISAM AUTO_INCREMENT=105431 DEFAULT CHARSET=utf8

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE `node_revisions` (
  `nid` int(10) unsigned NOT NULL DEFAULT '0',
  `vid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `uid` int(11) NOT NULL DEFAULT '0',
  `title` varchar(255) NOT NULL DEFAULT '',
  `body` longtext NOT NULL,
  `teaser` longtext NOT NULL,
  `log` longtext NOT NULL,
  `timestamp` int(11) NOT NULL DEFAULT '0',
  `format` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`vid`),
  KEY `nid` (`nid`),
  KEY `uid` (`uid`),
  FULLTEXT KEY `keywords` (`title`,`body`)
) ENGINE=MyISAM AUTO_INCREMENT=105431 DEFAULT CHARSET=utf8

Code :

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

1
2
3
4
5
6
7
8
9
CREATE TABLE `term_node` (
  `nid` int(10) unsigned NOT NULL DEFAULT '0',
  `vid` int(10) unsigned NOT NULL DEFAULT '0',
  `tid` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`tid`,`vid`),
  KEY `vid` (`vid`),
  KEY `nid` (`nid`),
  KEY `tid` (`tid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

Code :

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

1
2
3
4
5
6
7
CREATE TABLE `history` (
  `uid` int(11) NOT NULL DEFAULT '0',
  `nid` int(11) NOT NULL DEFAULT '0',
  `timestamp` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`uid`,`nid`),
  KEY `nid` (`nid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

Code :

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

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
SELECT
	node.nid AS nid,
	v.title AS node_title,
	v.body AS node_revisions_body,
	v.format AS node_revisions_format,
	DATE_FORMAT(FROM_UNIXTIME(node.created), '%Y%m%d%H') AS node_created_hour
FROM
	node
	INNER JOIN node_revisions v ON node.vid = v.vid
	INNER JOIN term_node tn1 ON tn1.nid = node.nid
	INNER JOIN term_node tn2 ON tn2.nid = node.nid
WHERE
1 NOT IN (SELECT h.uid FROM history h WHERE h.nid = node.nid) /*pour ne pas proposer des articles déjà lu par l'utilisateur*/
AND node.type = 'feed_item'
AND node.status = 1 /*seulement les nodes "publiés"*/
AND (
	(tn1.tid IN (15937, 12628))
	OR
	(MATCH(v.title, v.body) AGAINST ('keyword1 keyword2' IN BOOLEAN MODE))
)
AND (
	(tn2.tid IN (3574))
	OR
	(MATCH(v.title, v.body) AGAINST ('keyword3' IN BOOLEAN MODE))
)
GROUP BY node.nid
ORDER BY node.nid DESC

Merci beaucoup pour ton aide

[CinePhil]

Dans ta requête :

Code :

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

MATCH(node.title, node.body)

Or l'index FULLTEXT est sur la table 'node_revisions' !

EDIT :
La requête que tu donnes dans ton dernier message n'est pas la même que dans le premier !

Ça ressemble à du Drupal ta structure !

Plutôt que ceci :

Code :

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

1
2
WHERE
1 NOT IN (SELECT h.uid FROM history h WHERE h.nid = node.nid) /*pour ne pas proposer des articles déjà lu par l'utilisateur*/

Essaie plutôt ceci (si j'ai bien compris le système) :

Code :

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

1
2
3
4
5
6
7
WHERE NOT EXISTS
(
  SELECT *
  FROM hitory h
  WHERE h.nid = node.nid
    AND h.uid = 1 -- C'est l'identifiant de l'utilisateur ?
)

Pourquoi un GROUP BY alors qu'il n'y a pas de fonction d'agrégation dans le SELECT ?

[CinePhil]

Vite fait, tu prends les deux requêtes de ton message #10 et tu mets UNION entre les deux en enlevant le premier ORDER BY qui ne sert à rien.

Ça donne ça :

Code :

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

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
SELECT
	node.nid AS nid,
	v.title AS node_title,
	v.body AS node_revisions_body,
	v.format AS node_revisions_format,
	DATE_FORMAT(FROM_UNIXTIME(node.created), '%Y%m%d%H') AS node_created_hour
FROM
	node
	INNER JOIN node_revisions v ON node.vid = v.vid
WHERE
NOT EXISTS
(
  SELECT *
  FROM history h
  WHERE h.nid = node.nid
    AND h.uid = 1
)
AND node.type = 'feed_item'
AND node.STATUS = 1
AND (
	(MATCH(v.title, v.body) AGAINST ('keyword1 keyword2' IN BOOLEAN MODE))
)
AND (
	(MATCH(v.title, v.body) AGAINST ('keyword3' IN BOOLEAN MODE))
)
UNION
SELECT
	node.nid AS nid,
	v.title AS node_title,
	v.body AS node_revisions_body,
	v.format AS node_revisions_format,
	DATE_FORMAT(FROM_UNIXTIME(node.created), '%Y%m%d%H') AS node_created_hour
FROM
	node
	INNER JOIN node_revisions v ON node.vid = v.vid
	INNER JOIN term_node tn1 ON tn1.nid = node.nid
	INNER JOIN term_node tn2 ON tn2.nid = node.nid
WHERE
NOT EXISTS
(
  SELECT *
  FROM history h
  WHERE h.nid = node.nid
    AND h.uid = 1
)
AND node.type = 'feed_item'
AND node.STATUS = 1
AND (
	(tn1.tid IN (15937, 12628))
)
AND (
	(tn2.tid IN (3574))
)
ORDER BY node.nid DESC

À essayer.

[lr]

Merci. J'ai essayé et ça a fonctionné, la requête s'est exécutée en 2,3 sec, ce qui pourrait être acceptable. J'ai seulement du supprimer le dernier ORDER BY à cause de l'erreur "Unknown column 'node.nid' in 'order clause'"

Par contre, je m'interroge sur le sens de la requête. Si j'ai bien compris, elle va faire un UNION des résultats de la première et de la deuxième. Ce qui n'est pas forcément juste.

Ce dont j'ai besoin, c'est qu'elle me trouve les articles
que l'utilisateur n'a pas déjà lu
ET
dont le type est "feed_item"
ET
qui sont publiés
ET
qui sont taggés avec 15937 OU 12628 OU qui contiennent "keyword1" OU "keyword2"
ET
qui sont taggés avec 3574 OU qui contiennent "keyword3".

[CinePhil]

En ce cas il faut faire une jointure.

Et vu les conditions :

ET
qui sont taggés avec 15937 OU 12628 OU qui contiennent "keyword1" OU "keyword2"
ET
qui sont taggés avec 3574 OU qui contiennent "keyword3".

Il va falloir mélanger du FULLTEXT et du normal quand même.

As-tu essayé la requête qui ne prendrait que la première partie :

qui sont taggés avec 15937 OU 12628 OU qui contiennent "keyword1" OU "keyword2"

Si le temps est convenable, essaie la requête avec la deuxième partie puis fais une jointure entre les deux requêtes.

Pas le temps de regarder en détail maintenant mais l'ensemble devrait ensuite être simplifiable.

[lr]

Ceci fonctionne mais est très long (77 secondes) :

Code :

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

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
SELECT
	node.nid AS nid,
	v.title AS node_title,
	v.body AS node_revisions_body,
	v.format AS node_revisions_format,
	DATE_FORMAT(FROM_UNIXTIME(node.created), '%Y%m%d%H') AS node_created_hour
FROM
	node
	INNER JOIN node_revisions v ON node.vid = v.vid
	INNER JOIN term_node tn1 ON tn1.nid = node.nid
WHERE
NOT EXISTS
(
  SELECT *
  FROM history h
  WHERE h.nid = node.nid
    AND h.uid = 1
)
AND node.type = 'feed_item'
AND node.STATUS = 1
AND (
	(tn1.tid IN (15937, 12628))
	OR
	(MATCH(v.title, v.body) AGAINST ('solar tata test' IN BOOLEAN MODE))
)
 
UNION
 
SELECT
	node.nid AS nid,
	v.title AS node_title,
	v.body AS node_revisions_body,
	v.format AS node_revisions_format,
	DATE_FORMAT(FROM_UNIXTIME(node.created), '%Y%m%d%H') AS node_created_hour
FROM
	node
	INNER JOIN node_revisions v ON node.vid = v.vid
	INNER JOIN term_node tn2 ON tn2.nid = node.nid
WHERE
NOT EXISTS
(
  SELECT *
  FROM history h
  WHERE h.nid = node.nid
    AND h.uid = 1
)
AND node.type = 'feed_item'
AND node.STATUS = 1
AND (
	(tn2.tid IN (3574))
	OR
	(MATCH(v.title, v.body) AGAINST ('test2' IN BOOLEAN MODE))
)

Voilà le explain :

Code :

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

1
2
3
4
5
6
7
8
9
10
id,	select_type,		table,		type,	possible_keys,			key,		key_len,	ref,			rows,	Extra
1,	PRIMARY,		node,		ALL,	"PRIMARY,vid,node_type",	NULL,		NULL,		NULL,			40260,	"Using where"
1,	PRIMARY,		v,		eq_ref,	PRIMARY,			PRIMARY,	4,		ren21.node.vid,		1,
1,	PRIMARY,		tn1,		ref,	"PRIMARY,nid,tid",		nid,		4,		ren21.node.nid,		12,	"Using where"
2,	"DEPENDENT SUBQUERY",	h,		eq_ref,	"PRIMARY,nid",			PRIMARY,	8,		"const,ren21.node.nid",	1,	"Using where; Using index"
3,	UNION,			node,		ALL,	"PRIMARY,vid,node_type",	NULL,		NULL,		NULL,			40260,	"Using where"
3,	UNION,			v,		eq_ref,	PRIMARY,			PRIMARY,	4,		ren21.node.vid,		1,
3,	UNION,			tn2,		ref,	"PRIMARY,nid,tid",		nid,		4,		ren21.node.nid,		12,	"Using where"
4,	"DEPENDENT SUBQUERY",	h,		eq_ref,	"PRIMARY,nid",			PRIMARY,	8,		"const,ren21.node.nid",	1,	"Using where; Using index"
NULL,	"UNION RESULT",		"<union1,3>",	ALL,	NULL,				NULL,		NULL,		NULL,			NULL,

Comme j'ai de très sérieux doute sur la faisabilité, j'ai déjà commencé à m'orienter vers une autre solution. Donc ce n'est peut-être pas la peine de passer encore trop de temps là-dessus même si une solution serait l'idéal.

Quoi qu'il en soit, merci beaucoup pour ton aide.