SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    $books++ == true matsko's Avatar
    Join Date
    Sep 2004
    Location
    Toronto
    Posts
    795
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question One gigantic update query or multiple single queries?

    I've setup a late night cron job that updates the totals within the database everyday. It simply just collects a total count of the rows in other tables and updates another table with the row count values.

    My query is exactly this:
    Code:
    SELECT c.id,c.column_id,c.value,count(*) as m,u.user_id 
    FROM site_columns_values c 
    LEFT JOIN users_columns u ON c.id=u.value_id 
    GROUP BY (c.id) 
    ORDER by c.id
    The result set looks like this:

    Code:
    0, 2, 'male', 5, 1
    1, 2, 'female', NULL, 1, 
    2, 3, 'aquarius', 3, 2
    3, 3, 'aries', NULL, 1, 
    4, 3, 'cancer', NULL, 1,
    The last two columns are the userID and the count. If there are no matching userIDs in the column (NULL) then the count (the final column) should be 0 (but for some reason its displaying as 1).

    Well anywhoo, I've setup a php script to parse the results and if there is no matching userID value in the result set (if the value is equal to NULL) then the total is set to 0.

    Now I'm debating whether I should perform a database count update using one update like so:

    Code:
    UPDATE site_columns_values
       SET total = case id 
       when 1 then 100 
       when 2 then 2200
       when 3 then 124240 end
    WHERE id in (1,2,3)
    or hundreds of multiple queries like so:

    Code:
    UPDATE site_columns_values SET total = '.$totals[$i].' WHERE id='.$ids[$i].' LIMIT 1');
    Would the former be too large of a query? Do you think that it would cause lots of load to update say 400 columns?
    I can't believe I ate the whole thing

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    you should do neither. instead, fix your first query. when you've got it right, then you can change it to an UPDATE query and let mysql do all the heavy lifting.

    can you post some sample rows so we can fix your first query?
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  3. #3
    $books++ == true matsko's Avatar
    Join Date
    Sep 2004
    Location
    Toronto
    Posts
    795
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK well I'm having a similar issue with other Updates as well.

    I have two tables (forums and topics):

    tbl_topics
    id | section_id | name | user_id | replies | date

    tbl_forums
    id | name | topics | posts

    this is the query I'm using

    Code:
    select f.id,f.name,count(*) as topics FROM tbl_forums f LEFT JOIN 
    tbl_topics t ON f.id=t.section_id GROUP BY f.id;
    Now I get a result set that looks like so:

    1, 'General', 7

    The total is correct, however, all the other sections don't show up (since they have no linked topics), but I would still like to get each value.

    So something like this

    1, 'General', 7
    2, 'Support', 0
    3, 'Something Else', 0

    Then after that, I would like to update the existing forum sections.

    Code:
    UPDATE forums SET ... new totals ...
    I have no idea how todo this using one query ... is it possible?
    I can't believe I ate the whole thing

  4. #4
    $books++ == true matsko's Avatar
    Join Date
    Sep 2004
    Location
    Toronto
    Posts
    795
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK I have something close now:

    select f.id,f.name,IF(t.id IS NULL,0,count(*)) as t FROM forums_sections f LEFT OUTER JOIN forums_topics t ON f.id=t.section_id GROUP BY f.id;

    This provides the total count for each section of topics.


    Now how do I perform an update (update the forums sections total topics with these values)?

    Can this is only be done with temporary tables?


    --- EDIT ---

    I've noticed that this works perfectly if I use a subquery.

    UPDATE forums_sections s SET s.topics = (SELECT count(*) FROM forums_topics t WHERE t.section_id=s.id) WHERE s.id=s.id
    Last edited by matsko; Jun 1, 2009 at 23:14.
    I can't believe I ate the whole thing

  5. #5
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    the WHERE s.id=s.id is pointless. it's the same table! (and no, mysql can't automagically guess that one refers to the subquery and the other doesn't.)

    and a correlated subquery is probably one of the least-efficient ways to accomplish this.

    using a joined update will probably be faster:
    Code:
    UPDATE forums_sections s 
    LEFT OUTER
      JOIN forums_topics t
        ON t.section_id=s.id
       SET s.topics = COUNT(t.section_id)
    do you see why your first attempt was wrong? notice how you used count(*). that was count the total number of "collapsed" row, regardless of the number of rows in the LEFT JOIN'd table. my query specifically references a column in the joined table that i know will have a non-NULL value.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  6. #6
    $books++ == true matsko's Avatar
    Join Date
    Sep 2004
    Location
    Toronto
    Posts
    795
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, I tried the count(column) instead of the count(*) and it worked.

    However, the query that you provided above does not ... it returns an error saying invalid use of group function.
    I can't believe I ate the whole thing


Bookmarks

Posting Permissions

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