如何告诉MySQL优化器使用派生表上的索引？

Question

假设你有这样的查询..。

SELECT T.TaskID, T.TaskName, TAU.AssignedUsers
FROM `tasks` T
    LEFT OUTER JOIN (
        SELECT TaskID, GROUP_CONCAT(U.FirstName, ' ',
            U.LastName SEPARATOR ', ') AS AssignedUsers
        FROM `tasks_assigned_users` TAU
            INNER JOIN `users` U ON (TAU.UserID=U.UserID)
        GROUP BY TaskID
    ) TAU ON (T.TaskID=TAU.TaskID)

可以为一个给定的任务分配多个人。此查询的目的是在每个任务中显示一行，但在一个列中显示分配给该任务的人员。

现在..。假设您在tasks、users和tasks_assigned_users上设置了适当的索引。MySQL优化器在将tasks连接到派生表时仍然不会使用tasks索引。WTF？！？

我的问题是..。如何使该查询使用tasks_assigned_users.TaskID上的索引？临时桌子很烂，所以如果这是唯一的解决办法.MySQL优化器是愚蠢的。

使用的索引：

• 任务
  • 初级- TaskID

​

• 用户
  • 初级- UserID

​

• tasks_assigned_users
  • 初级- (TaskID，UserID)
  • 附加索引唯一- (UserID，TaskID)

​

编辑：也是，此页说派生表是在联接发生之前执行/物化的。为什么不重新使用键来执行连接呢？

编辑2: MySQL优化器不允许将索引提示放在派生表上(大概是因为派生表上没有索引)

编辑3:这里有一篇很好的关于这个的博客文章：http://venublog.com/2010/03/06/how-to-improve-subqueries-derived-tables-performance/注意到第二种情况是我想要的解决方案，但是现在看来MySQL并不支持这个问题。:(

编辑4:刚刚找到这：“从MySQL 5.6.3开始，优化器更有效地处理FROM子句中的子查询(即派生表)：.在查询执行期间，优化器可以向派生表添加索引，以加快从它检索行的速度。”看起来很有希望..。

Answer 1

在MySQL服务器5.6中有一个解决方案-预览版(在撰写本文时)。

http://dev.mysql.com/doc/refman/5.6/en/from-clause-subquery-optimization.html

不过，我不确定MySQL优化器在“向派生表中添加索引”时是否会重用已经存在的索引。

考虑以下查询：

从t1联接(从t2中选择*)选择*作为t1.f1=derived_t2.f1上的derived_t2；

文档中说：“如果这样做允许使用ref来实现最低成本的执行计划，那么优化器就会从f1中构造一个索引。”

好的，这很好，但是优化器会重用来自t2的索引吗？换句话说，如果存在t2.f1的索引怎么办？这个索引是被重用了，还是优化器为派生表重新创建了这个索引？谁知道呢？

编辑:在 5.6之前，最好的解决方案是创建一个临时表，在该表上创建一个索引，然后在临时表上运行SELECT查询。

Answer 2

我看到的问题是，通过执行子查询，没有底层的索引表。如果您正在进行一次表演，我会在最后进行分组，如下所示：

SELECT T.TaskID, T.TaskName, GROUP_CONCAT(U.FirstName, ' ', U.LastName SEPARATOR ', ') AS AssignedUsers
FROM `tasks` T
    LEFT OUTER JOIN  `tasks_assigned_users` TAU ON (T.TaskID=TAU.TaskID)
    INNER JOIN `users` U ON (TAU.UserID=U.UserID)
GROUP BY T.TaskID, T.TaskName

Answer 3

恐怕是不可能。必须创建临时表或视图才能使用索引。