Help with auto suggest

Hello !

I have a table that has about 10000 items. Each record has a field ITEM Name, and that has about 4-10 words / numbers in it.

I have a search box, there user can type and then an autosuggest dropdown appears and user can select the suggest word. (Like google search)

To achieve this, I am using below code, and I just want to know how to improve the below code so that it takes the minimum time:


		$sql = select_query("myItems","DISTINCT itemName","where LOWER(mi.itemName) LIKE '%".strtolower($search_query)."%'");
		$list = array();
		$words = array();
		
		while($r = mysql_fetch_object($sql))
		{
			$itemName = utf8_encode(html_entity_decode($r->itemName));
			$words = explode(" ",$itemName);
			for($a=0; $a < sizeof($words); $a++)
			{
				$word = strtolower($words[$a]);
				if(strlen(strpos($word,strtolower($q)))>0)
				{
					if(!in_arrayi($words[$a]."##".$words[$a],$list))
					{
						$list[] = $words[$a]."##".$words[$a];
					}
				}
			}
		}

NOTE: ## is a separator to be used in Jquery’ auto suggest plugin to separate the Value and Display text for the suggestion dropdown.

Please Guide !
ZH

You could add a LIMIT to your sql search. If you are only providing 8 suggestions to the user than you only need the first 8 results from the sql query. The search should run again with the next letter the user enters in to the field.

We have no idea what kind or size of objects/functions are being invoked by your select_query() call.

I have no idea of the cost of running LOWER() on your database column, but it probably nullifies any benefits of you having indexed that column - so that is going to slow things down.

You could try these things:

Benchmark some “naked” sql queries.

Tell jQuery to lower case the search text on its way out to you instead of doing that in PHP.

If itemName is a big search target, then consider having the text already lower-cased in an extra column as you enter the data (YMMV depending on how many inserts you do per day). Index that column.

Double check that the synonym mi. is actually set somewhere.

  1. Think about setting up Sphinx Indexer for mysql, it will run your query in milliseconds, I mean a full text search.

  2. You may also set searchable Column collation to case-insensitive.

Thanks a lot ! You people are great !