Increase MySQL LIKE query speed?

Question

For the entry field at the airport with autocompletion, there is currently one table with descriptions of airports, autocomplete_airport :

 lang | description (with INDEX) | ... -----+----------------------------------------------------+---- pt | New York - John F Kennedy (JFK), Estados Unidos | ... pt | Nova Iorque - John F Kennedy (JFK), Estados Unidos | ... ... 

AutoComplete works on individual words. Therefore, when the user enters "yor", "new york" appears (if in LIMIT). The request currently works as follows:

 SELECT * FROM autocomplete_airport WHERE lang = "pt" AND (description LIKE "%(yor)%" OR description LIKE "yor%" OR description LIKE "% yor%") ORDER BY description LIMIT 15 

Now I wonder how to speed up the work. One idea is to create the following database structure with the autocomplete_airport and autocomplete_airport_word :

 id | lang | description (with INDEX) | ... -----+------+----------------------------------------------------+---- 123 | pt | New York - John F Kennedy (JFK), Estados Unidos | ... 124 | pt | Nova Iorque - John F Kennedy (JFK), Estados Unidos | ... ... word (with INDEX) | autocomplete_airport_id ------------------+------------------------ New | 123 York | 123 John | 123 F | 123 Kennedy | 123 JFK | 123 ... 

Then SELECT would need to be searched only at the beginning of the lines:

 SELECT DISTINCT autocomplete_airport.* FROM autocomplete_airport INNER JOIN autocomplete_airport_word ON autocomplete_airport.id = autocomplete_airport_word.autocomplete_airport_id WHERE lang = "pt" AND word LIKE "yor%" ORDER BY description LIMIT 15 

Is this new design worth it? Will this really speed things up? Is there an easier way?

Update

Just noticed that the word table has a flaw. Consequence: a search for “New York” will not yield any results. What should work:

 term (with INDEX) | autocomplete_airport_id ------------------------------------------------+------------------------ New York - John F Kennedy (JFK), Estados Unidos | 123 York - John F Kennedy (JFK), Estados Unidos | 123 John F Kennedy (JFK), Estados Unidos | 123 F Kennedy (JFK), Estados Unidos | 123 Kennedy (JFK), Estados Unidos | 123 (JFK), Estados Unidos | 123 Estados Unidos | 123 Unidos | 123 JFK | 123 

Answer 1

As MartinK says, if your table has only a few hundred rows, your query should be pretty fast even without optimization - it's worth checking what happens.

However, the best way to search for text fields is to use full-text indexing (http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html) - this is for what you are describing.

Answer 2

Your suggested approach may speed up your request. Important information about LIKE queries is that the% pattern template should not be at the beginning of the template.

LIKE '%foobar' cannot use index. LIKE 'foobar%' can use an index.

However, if you only have a few airports to store, I would think twice if a full table scan really hurts.

Use EXPLAIN select {rest of query} to find out how and if the DB uses.

See http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html (Scroll down to the B-Tree Index Characteristics, this is the default mysql index type)