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)
         

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)
         

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'

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 below
         ) 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 below
t:   (I assume you have PRIMARY KEY(id))
tt:  INDEX(value, task_id)
p:   (I assume you have PRIMARY KEY(id))


         

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)