How do I get this lightning fast search?

I just came over this site: http://www.hittaplagget.se. If you enter the following search word moo, the autosuggest pops up immediately.

But if you go to开发者_开发技巧 my site, http://storelocator.no, and use the same search phrase (in "Search for brand" field), it takes a lot longer for autosuggest to suggest anything.

I know that we can only guess on what type of technology they are using, but hopefully someone here can do an educational guess better than I can.

In my solution I only do a SELECT moo% FROM table and return the results. I have yet not indexed my table as there are only 7000 rows in it. But I'm thinking of indexing my tables using Lucene.

Can anyone suggest what I need to do in order to get equally fast autosuggest?

You must add an index on the column holding your search terms, even at 7000 - otherwise, the database searching through the whole list every time. See http://dev.mysql.com/doc/refman/5.0/en/create-index.html.

Lucene is a full text search index and may or may not be what you're looking for. Lucene would find any occurrence of "moo" in the entire indexed column (e.g. Mootastic and Fantasticmoo) and does not necessarily speed up your search although it's faster than a where x like '%moo%' type of search.

As others have already pointed out a regular index (probably even unique?) is what you want if you're performing "starts with" type of searches.

You will need to table-scan the table, so I suggest:

• Don't put any rows in the table you don't need - for example, "inactive" records - keep them in a different table
• Don't put any columns in the table you don't need
• You can achieve this by having a special "Search table" which just contains the rows/columns you're interested in, and updating it from the "Master table".

Table-scanning a 7000 row table should be extremely efficient if the rows are small; I understand from your problem domain that this will be the case.

But as others have pointed out - don't send the 7000 rows to the client-side when it doesn't need it.

A conventional index can optimise a LIKE 'someprefix%' into a range-scan, so it is probably helpful having one. If you want to search for the string in any part of the entry, it is going to be a table-scan (which should not be slow on such a tiny table!)