选择所有具有匹配标签的项目

本文介绍了选择所有具有匹配标签的项目的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在尝试找到最有效的方式来处理此问题，但我必须告诉大家，我已经弄糟了.环顾四周，没有发现任何相关性，所以就去了.

I'm trying to find the most efficient way of dealing with this but I must tell you front-head I've made a mess of it. Looked around SO and found nothing of relevance so here it goes.

如何选择与所需项目具有相似标签的所有项目?

以该表为例: (以下用于重新创建表的sql代码)

Take this table for example: (sql code to recreate tables bellow)

project 1 -> tagA | tagB | tagC project 2 -> tagA | tagB project 3 -> tagA project 4 -> tagC

选择项目1应该返回所有项目. 选择项目4应该只返回项目项目1

Selecting project 1 should return back all projects. Selecting project 4 should only return project project 1

到目前为止，我的查询完全依赖于左联接，并且可以确定有更好的方法:

My query so far is pretty dependant of left joins and for sure there is a better way to do this:

SELECT all_tags.project_id, all_tags.tag_id, final.title, tag.tag FROM projects AS p LEFT JOIN projects_to_tags AS pt ON p.num = pt.project_id LEFT JOIN projects_to_tags AS all_tags ON pt.tag_id = all_tags.tag_id LEFT JOIN projects AS final ON all_tags.project_id = final.num LEFT JOIN tags AS tag ON all_tags.tag_id = tag.tag_id WHERE p.num = 4 GROUP BY final.num

谢谢大家的投入.尽管我会与大家分享100k项目数据库，100k标签数据库和100k projects_to_tags关系的所有查询的平均结果.所有查询均更改为要求project_1.

Thank you all for the input. I though I'd share with you guys the average results of all the queries on a 100k projects database, 100k tags database with a 100k projects_to_tags relation. All queries were changed to ask for project_1.

又短又甜:

0.0160 sec - OMG Ponies - Using JOINS 0.0208 sec - jdelard 0.2581 sec - OMG Ponies - Using EXISTS 0.2777 sec - OMG Ponies - Using IN 0.5295 sec - Emtucifor - updated query 0.5088 sec - Emtucifor - first query

非常感谢大家.将相应地更新我的所有查询.

Thank you all very much for this. Gonna update ALL my queries accordingly.

在这里进行所有查询以及相应的MySQL EXPLAIN以及时间

=============================================================================================================================================== Emtucifor - updated query =============================================================================================================================================== Showing rows 0 - 1 (2 total, Query took 0.5295 sec) SELECT * FROM projects AS L WHERE L.num !=1-- instead of <> PT2.project_id inside AND EXISTS ( SELECT 1 FROM projects_to_tags PT INNER JOIN projects_to_tags PT2 ON PT.tag_id = PT2.tag_id WHERE L.num = PT.project_id AND PT2.project_id =1 ) LIMIT 0 , 30 id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY L ALL PRIMARY NULL NULL NULL 100000 Using where 2 DEPENDENT SUBQUERY PT2 ref project_id project_id 4 const 1 Using index 2 DEPENDENT SUBQUERY PT ref project_id project_id 8 test.L.num,test.PT2.tag_id 12000 Using index =============================================================================================================================================== Emtucifor - first query =============================================================================================================================================== Showing rows 0 - 1 (2 total, Query took 0.5088 sec) SELECT * FROM projects AS L WHERE EXISTS ( SELECT 1 FROM projects_to_tags PT INNER JOIN projects_to_tags PT2 ON PT.tag_id = PT2.tag_id WHERE L.num = PT.project_id AND PT2.project_id =1 AND PT2.project_id <> L.num ) LIMIT 0 , 30 id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY L ALL NULL NULL NULL NULL 100000 Using where 2 DEPENDENT SUBQUERY PT2 ref project_id project_id 4 const 1 Using index 2 DEPENDENT SUBQUERY PT ref project_id project_id 8 test.L.num,test.PT2.tag_id 12000 Using where; Using index =============================================================================================================================================== jdelard =============================================================================================================================================== Showing rows 0 - 1 (2 total, Query took 0.0208 sec) SELECT p.num, p.title FROM projects_to_tags pt1, projects_to_tags pt2, projects p WHERE pt1.project_id =1 AND pt2.project_id !=1 AND pt1.tag_id = pt2.tag_id AND p.num = pt2.project_id GROUP BY pt2.project_id LIMIT 0 , 30 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE pt1 ref project_id project_id 4 const 1 Using index; Using temporary; Using filesort 1 SIMPLE pt2 index project_id project_id 8 NULL 75001 Using where; Using index 1 SIMPLE p eq_ref PRIMARY PRIMARY 4 test.pt2.project_id 1 =============================================================================================================================================== OMG Ponies - Using IN =============================================================================================================================================== Showing rows 0 - 2 (3 total, Query took 0.2777 sec) SELECT p . * FROM projects p JOIN projects_to_tags pt ON pt.project_id = p.num WHERE pt.tag_id IN ( SELECT x.tag_id FROM projects_to_tags x WHERE x.project_id =1 ) LIMIT 0 , 30 id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY pt index project_id project_id 8 NULL 100001 Using where; Using index 1 PRIMARY p eq_ref PRIMARY PRIMARY 4 test.pt.project_id 1 2 DEPENDENT SUBQUERY x ref project_id project_id 8 const,func 12000 Using where; Using index =============================================================================================================================================== OMG Ponies - Using EXISTS =============================================================================================================================================== Showing rows 0 - 2 (3 total, Query took 0.2581 sec) SELECT p . * FROM projects p JOIN projects_to_tags pt ON pt.project_id = p.num WHERE EXISTS ( SELECT NULL FROM projects_to_tags x WHERE x.project_id = 1 AND x.tag_id = pt.tag_id ) LIMIT 0 , 30 =============================================================================================================================================== OMG Ponies - Using JOINS =============================================================================================================================================== Showing rows 0 - 2 (3 total, Query took 0.0160 sec) SELECT DISTINCT p . * FROM projects p JOIN projects_to_tags pt ON pt.project_id = p.num JOIN projects_to_tags x ON x.tag_id = pt.tag_id AND x.project_id = 1 LIMIT 0 , 30 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE x ref project_id project_id 4 const 1 Using index; Using temporary 1 SIMPLE pt index project_id project_id 8 NULL 75001 Using where; Using index 1 SIMPLE p eq_ref PRIMARY PRIMARY 4 test.pt.project_id 1

用于复制/粘贴和混乱的SQL代码.

SQL code to copy/paste and mess around.

CREATE TABLE IF NOT EXISTS `projects` ( `num` int(2) NOT NULL auto_increment, `title` varchar(30) NOT NULL, PRIMARY KEY (`num`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ; INSERT INTO `projects` (`num`, `title`) VALUES(1, 'project 1'),(2, 'project 2'),(3, 'project 3'),(4, 'project 4'); CREATE TABLE IF NOT EXISTS `projects_to_tags` ( `project_id` int(2) NOT NULL, `tag_id` int(2) NOT NULL, KEY `project_id` (`project_id`,`tag_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO `projects_to_tags` (`project_id`, `tag_id`) VALUES(1, 1),(1, 2),(1, 3),(2, 1),(2, 2),(3, 1),(4, 3); CREATE TABLE IF NOT EXISTS `tags` ( `tag_id` int(2) NOT NULL auto_increment, `tag` varchar(30) NOT NULL, PRIMARY KEY (`tag_id`), UNIQUE KEY `tag` (`tag`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ; INSERT INTO `tags` (`tag_id`, `tag`) VALUES(1, 'tag a'),(2, 'tag b'),(3, 'tag c');

推荐答案

在以下任何一种情况下，如果您不知道PROJECT.num/PROJECT_TO_TAGS.project_id，则必须加入PROJECTS表以获取ID值，以找出其关联的标签.

In any of the following cases, if you don't know the PROJECT.num/PROJECT_TO_TAGS.project_id, you'll have to join to the PROJECTS table to get the id value for finding out what tags it has associated.

SELECT p.* FROM PROJECTS p JOIN PROJECTS_TO_TAGS pt ON pt.project_id = p.num WHERE pt.tag_id IN (SELECT x.tag_id FROM PROJECTS_TO_TAGS x WHERE x.project_id = 4)

使用EXISTS

SELECT p.* FROM PROJECTS p JOIN PROJECTS_TO_TAGS pt ON pt.project_id = p.num WHERE EXISTS (SELECT NULL FROM PROJECTS_TO_TAGS x WHERE x.project_id = 4 AND x.tag_id = pt.tag_id)

使用JOINS(这是效率最高的一种！)

DISTINCT是必需的，因为JOIN可能会在结果集中出现重复的数据...

Using JOINS (this the most efficient one!)

The DISTINCT is necessary because JOINs risk duplicated data turning up in the resultset...

SELECT DISTINCT p.* FROM PROJECTS p JOIN PROJECTS_TO_TAGS pt ON pt.project_id = p.num JOIN PROJECTS_TO_TAGS x ON x.tag_id = pt.tag_id AND x.project_id = 4