SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Member
    Join Date
    Jun 2008
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Speeding up a big site?

    Alright, so I'm using mysql with a site that has about 30,000 members and it's taking some members extremely long (like 15 minutes) to load their accounts.

    The way it works is, the script looks up the people you've referred, and a few people have about 1000 people referred

    then, for each individual person, then runs a query like SELECT SUM ... WHERE id=$individualperson (dumbing it down here to make it easier ) to figure out how many "points" they have

    loading the referral list is very quick - that is not the problem, but running those 1000 queries takes way too long - and this is on a duel xeon dedicated server. any tips/ideas? it doesn't matter what I have to change about the script (ie, not using mysql, whatever)

    Let me know if you want any more info
    Last edited by mauri824; Jun 18, 2008 at 08:23.

  2. #2
    SitePoint Wizard HarryR's Avatar
    Join Date
    Dec 2004
    Location
    London, UK
    Posts
    1,376
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ouch that looks like a major design problem.

    Firstly (the simple option) can you not condense it down into a single query with just another join against the person, points & referrers tables?

    As an alternative option, you could reverse the logic so when adding points you also update the referrer with an additional point in a separate column? You could add it pretty simply presuming your codebase is clean and well organized, then go back later and use the lengthy method for every user and add those points.

  3. #3
    SitePoint Member
    Join Date
    Jun 2008
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't think the alternative option would work
    you're right though, I can condense it down to one query using a left join, not sure why I hadn't done that already. I got that working now and I'll see what difference it makes

    it takes me an average of 0.01436s for each user now, on my comp (x2 5600+), so it'll take about 14-15 seconds for a thousand users
    I should also add a loading page meanwhile :P

    thanks!

  4. #4
    SitePoint Member
    Join Date
    Jun 2008
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    alright here's the query I re-did

    credit_value is how many individual points and total_credit is the sum of them

    Code:
    SELECT su.siteuser_id AS suid_referral, SUM( at.credit_value ) AS total_credit
    FROM site_user su, user u
    LEFT JOIN affiliate_track at ON at.siteuser_id = su.siteuser_id
    WHERE su.user_id = u.user_id  // this is to link 2 member tables, one of them has the member's profile, the other has the member's info for an individual site
    AND su.referred_by =  '1168' //necessary
    AND su.site_id =  '1'   // also necessary
    AND at.status =  '1'
    GROUP BY su.siteuser_id
    takes about 4 minutes to load 1000 users. it's better than 15 minutes but I have no idea what else I can do to speed this up. any tips/comments are welcome

    I just checked and this guy in particular has 1692 referred, ouch. and I was wrong, the site is smaller than I thought, there's only 35 thousand users

  5. #5
    SitePoint Wizard HarryR's Avatar
    Join Date
    Dec 2004
    Location
    London, UK
    Posts
    1,376
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Have you tried running the query plan explainer yet?

    e.g.
    Code:
    EXPLAIN SELECT ....
    Which will show the cardinality of each join, the indices it's using and any specific sorting options.

    It would be interesting to see that, I'm presuming you're lacking an index on the affiliate_track.siteuser_id column which might cause it to do a full table scan each time?

  6. #6
    SitePoint Member
    Join Date
    Jun 2008
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    here it is, but no, I hadn't tried that
    Attached Images Attached Images

  7. #7
    SitePoint Member
    Join Date
    Jun 2008
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    wow, thanks dude, it didn't have an index so I added one

    Showing rows 0 - 2 (3 total, Query took 0.3057 sec)
    / 3 * 1600 = about 2.7 minutes

    Getting faster....

  8. #8
    SitePoint Member
    Join Date
    Jun 2008
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Alright, sweet, I got it down to <1 second, how sick is that?
    Showing rows 0 - 1691 (1,692 total, Query took 0.0585 sec)

    I have one problem though, my query is this now:

    Code:
    SELECT su.siteuser_id AS suid_referral, SUM( at.credit_value ) AS total_credit
    FROM site_user su, user u
    
    LEFT JOIN affiliate_track at ON at.siteuser_id = su.siteuser_id
    WHERE su.user_id = u.user_id
    AND su.referred_by = '25631'
    AND su.site_id = '1'
    AND su.is_fulfilled = '0'
    GROUP BY su.siteuser_id
    but I only want it to add points when at.status = '1' , but if I add
    AND at.status = '1', it will remove a whole entry if at.status equals 0

    Is it impossible to do that with mysql? I'm guessing I'll have to select the individual credit values, and use php to add them up when at.status equals one

  9. #9
    SitePoint Wizard HarryR's Avatar
    Join Date
    Dec 2004
    Location
    London, UK
    Posts
    1,376
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by mauri824 View Post
    but I only want it to add points when at.status = '1' , but if I add
    AND at.status = '1', it will remove a whole entry if at.status equals 0
    Glad to see you've got it running faster.

    You can do this by specifying at.status in the join conditions, for example:

    Code:
    SELECT su.siteuser_id AS suid_referral, SUM( at.credit_value ) AS total_credit
    FROM site_user su, user u
    
    LEFT JOIN affiliate_track at ON( at.siteuser_id = su.siteuser_id AND at.status = 1 )
    WHERE su.user_id = u.user_id
    AND su.referred_by = '25631'
    AND su.site_id = '1'
    AND su.is_fulfilled = '0'
    GROUP BY su.siteuser_id
    Because the main query isn't dependent on rows in outer joins the main query will still execute if there are no matching rows in `affiliate_track`, when you specified at.status in the main query you're essentially transforming it into an inner join.


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
  •