Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2011
    Posts
    2

    Unanswered: Help needed with query optimization

    SELECT cc.collection_id,cc.title,cc.type,cc.alias as forum_alias,SUBSTRING(cc.description,1,200) as short_desc,
    (SELECT COUNT(boardmessage_id) FROM boardmessages WHERE parent_type='collection' AND collection_id=cc.collection_id AND is_active=1) as total_threads,(SELECT count(boardmessage_id) FROM boardmessages
    WHERE parent_type IN('message','reply','reply_on_reply') AND collection_id=cc.collection_id AND is_active=1) as total_replies
    FROM contentcollections cc WHERE cc.type=? AND cc.is_active=? ORDER BY cc.created DESC

    Is this the efficient way of querying the database because total_threada,total_replies both in the same table so multiple joining same table with contentcollections table also returns the wrong result.

    Any help or suggestion would be greatly appreciated.

    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT cc.collection_id
         , cc.title
         , cc.type
         , cc.alias AS forum_alias
         , SUBSTRING(cc.description,1,200) AS short_desc
         , COALESCE(bm.total_threads,0) AS total_threads
         , COALESCE(bm.total_replies,0) AS total_replies
      FROM contentcollections cc 
    LEFT OUTER
      JOIN ( SELECT collection_id
                  , COUNT(CASE WHEN parent_type = 'collection'
                               THEN 'humpty' END ) AS total_threads
                  , COUNT(CASE WHEN parent_type IN ('message','reply','reply_on_reply')
                               THEN 'dumpty' END ) AS total_replies
               FROM boardmessages 
              WHERE is_active = 1
             GROUP
                 BY collection_id ) AS bm
        ON bm.collection_id = cc.collection_id 
     WHERE cc.type=? 
       AND cc.is_active=? 
    ORDER 
        BY cc.created DESC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2011
    Posts
    2
    Quote Originally Posted by r937 View Post
    Code:
    SELECT cc.collection_id
         , cc.title
         , cc.type
         , cc.alias AS forum_alias
         , SUBSTRING(cc.description,1,200) AS short_desc
         , COALESCE(bm.total_threads,0) AS total_threads
         , COALESCE(bm.total_replies,0) AS total_replies
      FROM contentcollections cc 
    LEFT OUTER
      JOIN ( SELECT collection_id
                  , COUNT(CASE WHEN parent_type = 'collection'
                               THEN 'humpty' END ) AS total_threads
                  , COUNT(CASE WHEN parent_type IN ('message','reply','reply_on_reply')
                               THEN 'dumpty' END ) AS total_replies
               FROM boardmessages 
              WHERE is_active = 1
             GROUP
                 BY collection_id ) AS bm
        ON bm.collection_id = cc.collection_id 
     WHERE cc.type=? 
       AND cc.is_active=? 
    ORDER 
        BY cc.created DESC

    Hey Thanks for your reply , i am doing the same way like
    SELECT cc.collection_id, cc.title, cc.type, cc.alias AS forum_alias, SUBSTRING(cc.description,1,200) AS short_desc, COALESCE(bm.total_threads,0) AS total_threads, COALESCE(bm.total_replies,0) AS total_replies,COALESCE(bm.last_updated,0) AS last_updated FROM contentcollections cc LEFT OUTER JOIN ( SELECT collection_id, COUNT(CASE WHEN parent_type = 'collection' THEN 'humpty' END ) AS total_threads, COUNT(CASE WHEN parent_type IN ('message','reply','reply_on_reply') THEN 'dumpty' END ) AS total_replies,CASE WHEN parent_type IN ('message','reply','reply_on_reply') THEN DATE_FORMAT(created ,'%b %d,%Y at %r') END AS last_updated FROM boardmessages WHERE is_active = 1 GROUP BY collection_id ORDER BY created DESC ) AS bm ON bm.collection_id = cc.collection_id WHERE cc.type=1 AND cc.is_active=1 ORDER BY cc.created DESC;

    But it always return last_updated as 0.??

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    first of all, i can't read that without a lot of effort, and i'm lazy

    i already went to considerable trouble to format my previous reply, and you just disregarded that formatting? why?

    as far as your problem with the 0 date is concerned, i suggest you apply an aggegate to it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •