How to search fast tables columns for keyword in SQL?

I have a question about how I can search fast tables columns for specific keyword in SQL.

I have 3 tables.

Example

Table 1 has Makes

MakeID MakeName

Table 2 has Models

ModelID MakeID ModelName


Table 3 has Cars and Relationship to Table 1 and Table 2

CarID MakeID ModelID

The idea is the end user put keyword in search field like 'honda'. And it should return all cars from Table 3 where MakeName and ModelName contain word honda.

What I do is I select from Table 3 and join on Table 1 and Table 2 Where clause MakeName or ModelName like '%' + keyword + '%', also tables have indexes.

NOT sure if it's the most efficient way to do it. Because Table 3 contains 300K cars, so it takes time to search all tables.

Is there anyway to speed up the search? Any ideas the improve search highly appreciated. Maybe to mirror partially (only data that returned by search) information to another table and query it instead of main table?

Answers

Simple way:

1. Make sure you have indexes
2. Do search on Table 1 and 2 separately, take limited number of results as IDs
3. Query Table 3 as: select * from Table3 where modelId in (list of id's) or makeId in (list of Id's)

In another words - avoid joins

Advanced way: Setup full-text indexing

Need Your Help