optimise query help
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
user_Nick is indexed in userlog
SELECT user_Nick, COUNT( uservotelog_ID )
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
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
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?
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)
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?
oh ok, well there is no index on the Type column
it is an enum with values s, 1, 2, 3, 4
could you please indicate which of the two tables each of the columns in your query come from
the fields with uservotelog_ at the start are from the uservotelog table
the ones with user_ at the start are from the userlog table
try this, it may not be any faster but it's worth a shot --
select uservotelog_VoterUserID as voter
, count(*) as votes
where uservotelog_VoteeUserID = '1'
and uservotelog_Type = '4'
by votes desc
) as vt
on voter = user_ID
man, i don't know how you do it
but that takes less than half the time to execute on average
how can you know that one way is faster than another?
obviously it is how mysql processes the sql (order) right?
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