mysql - 我想不出有效的 SQL 查询来解决这个问题

Question

我有以下 SQL 表:

公司:

╔═════╦══════════╦═════════╦═══════════╦══════════════════╦═════╗
║  #  ║   Name   ║ Country ║   City    ║     Address      ║ Nr. ║
╠═════╬══════════╬═════════╬═══════════╬══════════════════╬═════╣
║ 1   ║ T-Online ║ Germany ║ Frankfurt ║ Teststr.         ║  15 ║
║ 2   ║ Telecom  ║ Italy   ║ Rome      ║ Via test         ║  20 ║
║ 3   ║ Verizon  ║ USA     ║ New York  ║ Something street ║  53 ║
║ ... ║ ....     ║         ║           ║                  ║     ║
╚═════╩══════════╩═════════╩═══════════╩══════════════════╩═════╝

公司标签:

╔═════════╦═════╗
║ Company ║ TID ║
╠═════════╬═════╣
║ 1       ║ 10  ║
║ 2       ║ 15  ║
║ 1       ║ 11  ║
║ 3       ║ 19  ║
║ 2       ║ 11  ║
╚═════════╩═════╝

标签:

╔════╦══════════════════════╗
║ ID ║ Name                 ║
╠════╬══════════════════════╣
║ 1  ║ Software Development ║
║ 2  ║ Hosting              ║
║ 3  ║ Restaurant           ║
║... ║ ....                 ║
╚════╩══════════════════════╝

(所有值均为示例，并非真实表格中的值!)

我需要搜索给定城市和国家/地区的公司并具有特定标签。例如，我搜索美国纽约所有带有软件开发标签的公司。我需要能够仅使用一个 SQL 查询来完成此操作。

我现在使用的是:

• 我搜索给定城市和国家/地区的所有公司
• 然后搜索给定标签的 ID
• 之后，我会搜索所有具有该标签 ID 的公司
• 最后，我会过滤表格公司以输出符合这些规则的所有公司。

显然这种方法不适合在生产中使用，对性能影响太大，不宜使用。

编辑:谢谢您的所有答案，我会尝试每一个，最有效的将是批准的答案:)

Answer 1

您可以使用 JOIN 并在公司表中添加国家和城市索引

SELECT Name
FROM companies AS c INNER JOIN tagsForCompany AS tc ON c.id = tc.Company
INNER JOIN tags AS t ON t.id = tc.TID
WHERE city = "your_city" AND country = "your_country" AND t.Name REGEXP 'your_tag'

在此查询中，将首先使用 INNER JOIN 生成一个表，然后根据生成的新表进行过滤

但是更好、更优化的解决方案可能是通过过滤城市和国家/地区使用子查询生成表。还要在公司表中添加国家和城市索引，因为这会减少很多时间。现在新的查询将是

SELECT c.Name
FROM 
  (SELECT id, Name
   FROM companies
   WHERE city = "your_city" AND country = "your_country" ) AS c
  INNER JOIN tagsForCompany AS tc ON c.id = tc.Company
  INNER JOIN tags AS t ON t.id = tc.TID
WHERE t.Name REGEXP 'your_tag'

添加索引的语法ALTER TABLE tbl_name ADD INDEX index_name (column_list)，它添加一个普通索引，其中任何值都可以出现多次。

ALTER TABLE companies ADD INDEX city (city);
ALTER TABLE companies ADD INDEX country (country);