slow query help

KashKurcura

hey you database people, i need some urgent help here if i can get it. i have this query that will only return members that user "1162828" hasnt already voted for. the reason behind this is so user "1162828" does vote on the same member more then once.

my problem is that it is slow and gets recored to my slow logs. anyone know why this might be slow?

my sql looks like this :

CREATE TABLE Votes (
VoteID mediumint(7) NOT NULL auto_increment,
MemberID mediumint(7) NOT NULL default '0',
VoterID mediumint(7) NOT NULL default '0',
Vote tinyint(2) NOT NULL default '10',
Date date NOT NULL default '0000-00-00',
PRIMARY KEY (VoteID),
KEY VDex (MemberID,VoterID),
KEY VDate (Date)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3871466 ;

my log report says stuff like this :

Time: 060411 13:43:32

Query_time: 4 Lock_time: 0 Rows_sent: 1 Rows_examined: 10963985

SELECT UserName, Photo, m.MemberID
FROM Members m
LEFT JOIN (SELECT MemberID FROM Votes WHERE VoterID=1162828) AS v USING(MemberID)
WHERE v.MemberID IS NULL
AND m.Public = 'Y'
ORDER BY RAND()
LIMIT 1;

anyone see what i might be doing wrong and have some suggestions?

bpat1434

I'm gonna say it's because you have 10 million rows (about to be 11 million)... so it's going through all those rows at once.... that's a lot!!

Have you tried optimizing the table at all?

KashKurcura

well there are only 2,257,894 recoreds total, so it must be doing something that it shouldnt. so i am kinda lost here.

m_tt

Have you tried changing the ORDER BY to a field that is known to be indexed? I'm not sure what the RAND() does? Actually is the ORDER BY even necessary since it's limited to 1 result?

Roger_Ramjet

You are doing a table scan in the subquery and with that many records it's always going to be slow.

You need to create indexes on the MemberID and on the VoterID, you should always index foriegn key columns that you are going to use in joins; and you don't need a subquery for this.

You want to get a random member who user "1162828" hasn't already voted for, I believe.

"SELECT UserName, Photo, MemberID
FROM Members m LEFT JOIN Voters v USING(MemberID)
WHERE (v.MemberID IS NULL OR v.VoterID <> 1162828) AND m.Public = 'Y'
ORDER BY RAND() LIMIT 1"

PS, you don't have to specify the table name with the USING(MemberID) form in a join as only 1 MemberID column is returned. One of the big advantages of the USING() form.