MySQL 搜索用户及其角色

Question

我正在重写 SQL，让用户可以在我们的网站上搜索任何其他用户并显示他们的角色。

例如，角色可以是“作家”、“编辑”、“出版商”。

每个角色都将一个用户链接到一个发布。

用户可以在多个出版物中担任多个角色。

示例表设置:

"users" : user_id, firstname, lastname
"publications" : publication_id, name  
"link_writers" : user_id, publication_id  
"link_editors" : user_id, publication_id  

当前的伪 SQL:

SELECT * FROM (
  (SELECT user_id FROM users WHERE firstname LIKE '%Jenkz%') 
  UNION 
  (SELECT user_id FROM users WHERE lastname LIKE '%Jenkz%')
) AS dt
JOIN (ROLES STATEMENT) AS roles ON roles.user_id = dt.user_id

目前我的角色声明是:

SELECT  dt2.user_id, dt2.publication_id, dt.role FROM (
  (SELECT 'writer' AS role, link_writers.user_id, link_writers.publication_id
  FROM link_writers)
  UNION
  (SELECT 'editor' AS role, link_editors.user_id, link_editors.publication_id
  FROM link_editors)
) AS dt2

将角色语句包装在 UNION 子句中的原因是某些角色更复杂，需要表连接才能找到 publication_id 和 user_id。

例如，“publishers”可能跨两个表链接

"link_publishers": user_id, publisher_group_id
"link_publisher_groups": publisher_group_id, publication_id

所以在那种情况下，构成我的 UNION 一部分的查询将是:

SELECT 'publisher' AS role, link_publishers.user_id, link_publisher_groups.publication_id
FROM link_publishers
JOIN link_publisher_groups ON lpg.group_id = lp.group_id

我非常有信心我的 table 设置很好(在研究布局时我被警告不要使用一 table 一 table 系统)。我的问题是现在用户表中有 100,000 行，每个链接表中有多达 70,000 行。

在 users 表中的初始查找很快，但加入确实减慢了速度。

如何才能只加入相关角色？

------------------------编辑-------------------- --------------

上面的解释(在新窗口中打开以查看完整分辨率)。

红色的底部位是“WHERE firstname LIKE '%Jenkz%'”，第三行搜索 WHERE CONCAT(firstname, ' ', lastname) LIKE '%Jenkz%'。因此行数很大，但我认为这是不可避免的，除非有一种方法可以跨连接的字段放置索引？

顶部的绿色位仅显示从 ROLES STATEMENT 扫描的总行数。

然后您可以看到每个单独的 UNION 子句(#6 - #12)，它们都显示了大量的行。有些索引是正常的，有些是唯一的。

似乎 MySQL 没有优化使用 dt.user_id 作为 UNION 语句内部的比较。有什么办法可以强制这种行为吗？

请注意，我的真实设置不是出版物和作者，而是“网站管理员”、“玩家”、“团队”等。

Answer 1

查看了 OMG Ponies 对 SO - Use Of Correlated Subquery 的回答，我想到了这个:

SELECT * FROM (
  (SELECT user_id FROM users WHERE firstname LIKE '%Jenkz%') 
  UNION 
  (SELECT user_id FROM users WHERE lastname LIKE '%Jenkz%')
) AS dt
JOIN ( SELECT 'writer' AS role, link_writers.user_id, link_writers.publication_id
       FROM link_writers
       UNION
       SELECT 'editor' AS role, link_editors.user_id, link_editors.publication_id
       FROM link_editors
       UNION
       SELECT 'publisher' AS role, lp.user_id, lpg.publication_id
       FROM link_publishers lp
       JOIN link_publisher_groups lpg ON lpg.publisher_group_id = lp.publisher_group_id
     ) roles on roles.user_id = dt.user_id

解释在我的小数据集上看起来很合理。它在真实的东西上看起来像什么？