Re: Searching Titles Based on Keywords

Yudong Sun, molten keyboard in hand, chimes in with: > > Here is a design question: > > I have a database of book titles each associated with several > keywords. How should I organize to have the best performance: > > - keywords associated with each title share one field separated > by comma; > > - each keyword occupies one field - the BOOK table contains > five keyword fields: KEYWORD1, KEYWORD2, ... > > Could someone tell me if the first approach is efficient enough > (assuming it's indexed normally)? Thanks in anticipation. > > Yudong We're using a keyword table with one keyword per row becuase it is rather difficult to query in the middle of a comma seperated list without table scanning, which tends to be bad. I'd like to see some other ideas on this. We've got a similiar thing going on, with keyword tied to document, one keyword per row. The keyword table gets rather large, and we're about to load it a bunch more. Sometimes the optimizer makes really stupid decisions when we make combined queries against the documents and the keywords (like running through the keywords first, and then checking documents when I'd prefer it did it the other way around). We're in the process of evaluating 7.xxx to see if its optimizer is a little bit smarter. But I'm more interested in other approaches to this kind of "keyword" problem using SQL DBs. Thanx, Will Hartung (villy@collinscomp.com)