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

Make the right rule for urlManager Yii

php yii routes

Good time. There was a question on setting rules for urlManager. Need to do to be able to pass id defaultController controller. For example, I defaultController = "default" and I need to pass it the

For each table row / cell with specific class function: Javascript - jQuery

javascript jquery jquery-selectors

I have a table. The table has some set of rows, each set containing 3 specific rows, and repeated downwards.. I want to have a variable i which should be incremented for each row (<tr>) having

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.