Skip navigation links

MySQL Forums :: Database Design & Data Modelling :: How to design


Advanced Search

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


Subject Written By Posted
How to design Reto Baumann 06/09/2006 09:02AM
Re: How to design Bob Field 06/09/2006 09:11AM
Re: How to design Reto Baumann 06/09/2006 09:25AM
Re: How to design Bob Field 06/09/2006 12:54PM
Re: How to design Reto Baumann 06/11/2006 12:54AM
Re: How to design Bob Field 06/11/2006 10:02AM
Re: How to design Reto Baumann 06/11/2006 11:31AM
Re: How to design Bob Field 06/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.