Return to Revisions

Let's turn it outside 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)