Re: How to design
Posted by: Bob Field
Date: June 11, 2006 10:02AM

My mistake, it meant to read g1.groupID in the first line:

SELECT g1.itemID, g1.groupID
FROM grouptest AS g1
LEFT JOIN grouptest AS g2 ON g1.groupID = g2.groupID AND g1.groupID != 0 AND g1.score < g2.score
WHERE keywords LIKE '%apple%'
AND g2.itemID IS NULL;

Performance of this query is potentially slow. Because of the leading '%' in the LIKE '%apple%' clause, the entire table needs to be scanned for potentially matching records. If you could guarantee that the keyword always appeared at the start of the column, the clause LIKE 'apple%' could perform much faster if the keywords field was indexed.

One possibility would be to adopt a normalized approach to storing keywords. Since items-to-keywords is a many-to-many relationship, ideally you should have all potential keywords listed in a separate table, and a bridge table with rows indicating which keywords are applicable to which item. Proper indexing will make querying by specific keywords very fast.

Options: ReplyQuote

Written By
June 09, 2006 09:02AM
June 09, 2006 09:11AM
June 09, 2006 09:25AM
June 09, 2006 12:54PM
June 11, 2006 12:54AM
Re: How to design
June 11, 2006 10:02AM
June 11, 2006 11:31AM
June 11, 2006 12:24PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.