Re: SQL join question

kgmahoney_at_yahoo.com (Kevin Mahoney) wrote in news:f775e61e.0210240751.25400370_at_posting.google.com:

> I am trying to have one SQL statement that returns all Products
> that have ALL the keywords in the keyword table exists somewhere
> in their name

Hi Kevin,

Rather than providing a solution for the example you've provided, I think you should reconsider your solution. The problem with it is that it'll never scale, unless you drastically alter the data structures. What you presented will be a table scan each and every time. This is a huge resource drain on the RDBMS. The more users you have doing this, the slower the response time. The degradation will be non-linear too! Yikes!

If you were to use an inverted index, it'll perform substantially better, however, before you go into the pains of doing that, you should look at the Inter-Media cartridge from Oracle. It's their keyword search engine.

As a side note, if you go down that path, look at writing your own filter/parser. It's easy and if you don't need to keyword WORD, PDF and the zillions of other docs that the default filter can parse, your parser will be significantly faster (8 to 9x if I recall correctly???)

-- 
Pablo Sanchez, High-Performance Database Engineering
http://www.hpdbe.com