Searching 15k address rows via autocomplete, MySQL like or full text search?

Question

I have a db table of Australian postcodes and addresses with 15k rows, each row looking like this:

pcode   |   suburb      |   state
2048    |   Westgate    |   NSW
2066    |   Belina      |   NSW

Triggered an input field with jQuery autocomplete, I split up the user input and build a LIKE query, searching on the suburb and postcode fields.

This does the trick:

SELECT address_id as id, CONCAT(suburb,' ',postcode) as value FROM address WHERE address_id != ''"; 

foreach ($keywords as $keyword)
{
      $query .= " AND (postcode like '".$keyword."%' OR suburb like '%".$keyword."%')";
}

I have no indexes on any columns at present (aside from primary key). Would I be better to use some sort of full text index and change the search? or given the amount of data (not huge), is what I'm doing efficient enough

Answer 1

The most efficient type of indexes are with numerical column indexes. as others suggested, 15k is not a big set of records , but in terms of user experience, when you type a single character, there can be large number of records thus making it a usability nightmare.

suggestion - use a LIMIT clause in your sql so that you can limit your resultset to a manageable size.. lets say 15 records at a time..