SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Evangelist
    Join Date
    Mar 2003
    Location
    Melbourne, Australia
    Posts
    463
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    optimise query help

    hi all

    i've been trying to make this query run faster
    i tried making it select from a subquery, but that was a little slower

    the query is used to select who has voted for me most

    Code:
      SELECT user_Nick, COUNT( uservotelog_ID ) 
     FROM urban_userlog
     INNER  JOIN urban_uservotelog ON user_ID = uservotelog_VoterUserID
     WHERE user_AccountStatus =  'a'
     AND uservotelog_VoteeUserID =  '1'
     AND uservotelog_Type =  '4'
     GROUP  BY uservotelog_VoterUserID
     ORDER  BY  `COUNT(uservotelog_ID)`  DESC 
     LIMIT 1
    user_Nick is indexed in userlog
    VoterUserID and VoteeUserID are indexed in uservotelog
    the times running through phpmyadmin are about .018 seconds to execute on average
    can anyone see a way to get it down a bit

    thanks
    dave

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    can't help you without seeing all the columns in both tables, all indexes on both tables, and why you are not grouping on the same column you are selecting

    if you say you've tried a subquery, this means you're on at least 4.1?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist
    Join Date
    Mar 2003
    Location
    Melbourne, Australia
    Posts
    463
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes, mysql 4.1.7
    all columns in both tables? the userlog table has 61 columns, 14 indexes
    the uservotelog table is not so bad 8 columns, 6 indexes
    isn't it enough just to mention the relevant fields?
    i'm afraid you might turn away in disgust if you see the userlog table

    group by, don't know, never thought to change it (i didn't do the original query)

    thanks
    dave

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    well, i don't really want to see them, i just figured it might poke you a little to reconsider which indexes are actually being used for the query

    for instance, the condition uservotelog_Type = '4' will likely require a sequential scan if there's no index on it

    have you done an EXPLAIN yet?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist
    Join Date
    Mar 2003
    Location
    Melbourne, Australia
    Posts
    463
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    oh ok, well there is no index on the Type column
    it is an enum with values s, 1, 2, 3, 4

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    could you please indicate which of the two tables each of the columns in your query come from
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Evangelist
    Join Date
    Mar 2003
    Location
    Melbourne, Australia
    Posts
    463
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    the fields with uservotelog_ at the start are from the uservotelog table
    the ones with user_ at the start are from the userlog table

    dave

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    try this, it may not be any faster but it's worth a shot --
    Code:
    select user_Nick
         , votes
      from (
           select uservotelog_VoterUserID  as voter
                , count(*)                 as votes
             from urban_uservotelog
            where uservotelog_VoteeUserID =  '1'
              and uservotelog_Type =  '4'
           group 
               by uservotelog_VoterUserID
           order
               by votes desc
           limit 1    
           ) as vt
    inner
      join urban_userlog
        on voter = user_ID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Evangelist
    Join Date
    Mar 2003
    Location
    Melbourne, Australia
    Posts
    463
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    man, i don't know how you do it
    but that takes less than half the time to execute on average
    thanks heaps

    how can you know that one way is faster than another?
    obviously it is how mysql processes the sql (order) right?

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    in this case the secret was knowing that you only want one result

    joining the user table to one result from the GROUP BY is apparently faster than grouping to get one result from a join to the user table

    yes, it is all about execution sequence
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •