optimise query help

[dtra]

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

  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

[r937]

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?

[dtra]

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

[r937]

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?

[dtra]

oh ok, well there is no index on the Type column
it is an enum with values s, 1, 2, 3, 4

[r937]

could you please indicate which of the two tables each of the columns in your query come from

[dtra]

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

[r937]

try this, it may not be any faster but it's worth a shot --

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

[dtra]

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?

[r937]

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