SQL query help

outofregs · #1 (**permalink**) 04-07-11, 16:21

Hello,

I have 2 Tables (keywords & archive). The archive table is my main table and stores data for submitted pictures. The keywords table stores all keywords used in my site with 3 columns (id, keyword & numclicks). Currently I have a section on my site (OutOfRegs - Your source for military humor!) that lists the top 20 keywords and orders them by the 'numclicks' column. There is a column in the archives table titled 'keywords' and contains a string of keywords separated by a comma (ex: military, army, fail). I want to perform a query that groups keywords by the number of times they show up in 'archive.keywords', and then order the results by 'keywords.numclicks'. This query would be better than the previous because it would order keywords by their frequency on the site aas well as their number of clicks. Any ideas?

r937 · #2 (**permalink**) 04-07-11, 22:22

comma-delimited values inside a single column violates first normal form, and the penalty you pay is complex queries and really terrible performance

Code:

SELECT k.keyword
     , COUNT(a.keyword) AS times_in_archive
  FROM keywords AS k
LEFT OUTER
  JOIN archive AS a
    ON FIND_IN_SET(k.keyword,a.keyword) > 0
GROUP
    BY k.keyword

Internet Services

Web Development

Digital Marketing

Consumer Tech