Optimizing a distinct on a parent table in a multi-level join in MySQL 5

Question

I have the following tables: in a MySQL 5 database:

• projects - 1,000 rows
• tasks (FKed to projects) - 10,000 rows
• task_tags (FKed to tasks) - 350,000 rows
• task_comments (FKed to tasks) - 750,000 rows

and want to get a list of projects for which any task has exactly the word urgent in a tag or comment, i.e.

select DISTINCT p.*
from projects p
join tasks t on p.id = t.project_id
left join task_tags tt on t.id = tt.task_id
left join task_comments tc on t.id = tc.task_id
where tt.value = 'urgent' OR tc.text = 'urgent'

Without the DISTINCT, the query executes in well under 0.1 second (and the indices used look sensible). The Visual Execution Plan is (with the tables being projects, tasks, task_tags and task_comments in that order from left to right):

Adding the DISTINCT makes the query seriously unperformant (on the order of minutes), with the DISTINCT being added on after the last nested loop, and thus I believe requiring MySQL to sort all the resulting rows, and then deduplicate them.

While this is correct, it does not feel like the best possible option--after all, once we know that a project meets the criteria, there is no point checking any other tasks for that project as this isn't e.g. select distinct p.*, t.id - but I don't know how to encourage MySQL to plan this more sensibly because I don't know what plan I want.

I have tried something along the lines of

select distinct project_id
from tasks
where id in (
    select task_id from task_tags where value = 'urgent'
    union
    select task_id from task_comments where text = 'urgent'
)

as an alternative but we are also on the order of minutes for that though I believe it is slightly faster, and I have no other great ideas (except for things not in MySQL 5 like persisted views, but upgrading the DB is not a practical option at this stage).

Any ideas as to what I could try (or even where to investigate next)? The database model is fairly fixed, but certainly I can add indices and happy to look at options.

Answer 1

Let's turn it inside-out so we can see that it is starting at the right places. The Optimizer won't do this work for us.

1. Start with each table that might say 'urgent'
2. UNION them. (UNION DISTINCT is slightly slower than UNION ALL, but you might get two duplicate rows. You decide.)
3. Join to tasks to get the project_id
4. Finally, reach into projects for the few rows that are needed. (Note how both of your formulations effectively require fetching all of p before figuring out that most of the rows aren't needed.)

Switching from OR to UNION was a good idea, but IN ( SELECT ... ) is not an efficient construct.

SELECT p.*
    FROM (
         SELECT t.project_id
            FROM task_comments tc
            JOIN tasks t  ON t.id = tc.task_id
            WHERE tc.text = 'urgent'  -- see Note
         ) UNION DISTINCT (
         SELECT t.project_id
            FROM task_tags tt
            JOIN tasks t  ON t.id = tt.task_id
            WHERE tt.value = 'urgent'
         ) AS x
    JOIN projects p  ON p.id = x.project_id

That will need

tc:  INDEX(text, task_id)  -- see Note
t:   (I assume you have PRIMARY KEY(id))
tt:  INDEX(value, task_id)
p:   (I assume you have PRIMARY KEY(id))

Note: Perhaps you really want to check for "urgent" anywhere in tc.text? If so, the best way to optimize it is to have

tc:  FULLTEXT(text)

and switch to

WHERE MATCH(tc.text) AGAINST ('+urgent' IN BOOLEAN MODE)