Re: performance question

Craig Taylor <craigtaylor2_at_home.com> wrote:

>I am about to set up a small search engine for my shopping site. I have just
>over 1100 products now, but I am expecting much more (knock on wood).

My guess is that you're doing this on a low budget and don't have beefy hardware or products like Verity or Oracle Intermedia. Based on that assumption... do one of two things:

Skip the index and do the full-table scan with a LIKE clause. We're talking fewer than 5000 rows here, which should take less than 1 second on a 500Mhz. Linux box once the data is cached in memory (assuming the descriptions are VARCHAR2(2000)'s with an average length of 200). This will get slower as you add OR's into the conditional clause, or put many wildcards into the search string.

Build an index table that has an index of uppercase keywords. You can start by indexing every word, then delete the words that are used heavily throughout or are very short. In other words, only keep meaningful words in the index. Hopefully limit each item to 10 or fewer keywords. The downside of this approach... it works ok with medium sized datasets, but on small sets of data sometimes the index lookups + row fetch + memory overheads will be slower than a brute force compare of all the rows.

The next thing you'll want to conquer is misspelled words, which the SOUNDEX function might come in handy for.

>My question has to do with the whether to break the product description
>field down into individual words and store them as new fields (and index
>them), or to use a "LIKE" query to search the description field for the
>word. Also, can I use indexing with a "LIKE".

LIKE uses an index only when the % is at the end of the statement. For example:

select 'x' from tab1 where col like 'game%' -- index can be used select 'x' from tab1 where col like '%game%' -- index cannot be used

It also is case sensitive, so you will need to build a function based index (if you're using Oracle8, otherwise add a field that has the lowercase'd description and index that) and search in a fixed case. Received on Tue Feb 20 2001 - 17:32:53 CST