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.
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?
- Make sure you have indexes
- Do search on Table 1 and 2 separately, take limited number of results as IDs
- 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