How to tell MySQL Optimizer to use an index in a view?

Question

Suppose you have such a request ...

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)

Several people can be assigned for this task. The purpose of this query is to show one row for each task, but with the people assigned to the task in one column

Now ... suppose you have the correct index setting on tasks, usersand tasks_assigned_users. The MySQL optimizer will still not use the TaskID index when joining a view tasks. WTF?!?!?

So my question is ... how can you make this query using the index on tasks_assigned_users.TaskID? Temporary tables are lame, so if this is the only solution ... MySQL Optimizer is stupid.

Indexes Used:

• tasks
  • PRIMARY - TaskID
• Users
  • PRIMARY - UserID
• tasks_assigned_users
  • PRIMARY - (TaskID, UserID)
  • UNIQUE - (UserID, TaskID)

EDIT: , , / , . ?

EDIT 2: MySQL ( , )

3: : http://venublog.com/2010/03/06/how-to-improve-subqueries-derived-tables-performance/ , Case # 2 , , , MySQL .: (

4: this: " MySQL 5.6.3 FROM (.. ):... , ". ...

Answer 1

MySQL Server 5.6 - ( ).

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

, MySQL Optimizer , , " "

:

SELECT * FROM t1 JOIN (SELECT * FROM t2) AS output_t2 ON t1.f1 = _t2.f1;

: " f1 _t2, ".

, , t2? , , t2.f1? ? ?

EDIT: , MySQL 5.6 , , SELECT temp.

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

I fear it is impossible. You must create a temporary table or view to use the index.