How to optimize a given query with Join?

Question

SQL Server

There are 2 tables, let's say First (id, name, message) and Second (id, value, message), each with a million records
. There is a query:

select First.id, First.name, Second.value
from First
join Second
on First.id =Second.id
where First.message like "%smth%" or Second.message like "%smth%"

It is clear that a million to a million joins and then the search goes on.
How can this query be optimized? Runs for a very long time

Answer 1

The problem is not in the join, but in the search bar. like "%smth%" means that the search will not use the index, which means it will iterate over the rows of the table. like "smth%" will give a much nicer picture if there is an index on messaqe (and it should be there in such a search). Full-text search is possible, but, of course, there are some pitfalls.
You can also make an index on name and use its / composite index in the search.

Answer 2

Full Text Search