I am trying to create a rating script of somesorts where it will only display people who you have not rated. When I add in a order by to the query it seems to take for ever and hang on me? Anyone have any suggestions?
CREATE TABLE Ratings_Entries (
MemberID mediumint(9) NOT NULL default '0',
Yes mediumint(7) NOT NULL default '0',
No mediumint(7) NOT NULL default '0',
Total mediumint(7) NOT NULL default '0',
Percent decimal(4,2) NOT NULL default '0.00',
PRIMARY KEY (MemberID)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE Ratings_History (
MemberID mediumint(9) NOT NULL default '0',
RaterID mediumint(9) NOT NULL default '0',
Rating enum('Y','N') NOT NULL default 'N',
KEY 9542 (RaterID)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
The below query is very fast
SELECT Ratings_Entries.MemberID
FROM Ratings_Entries
WHERE MemberID NOT IN (SELECT MemberID FROM Ratings_History WHERE RaterID = '112')
LIMIT 1
When i try to do a order by rand() the query seems to take forever and hang on me
SELECT Ratings_Entries.MemberID
FROM Ratings_Entries
WHERE MemberID NOT IN (SELECT MemberID FROM Ratings_History WHERE RaterID = '112')
ORDER BY RAND()
LIMIT 1
There is data in both tables , and i know that order by rand isnt the best, but if i do any order at all, it still hangs, if i select more then 1 it hangs as well. any suggestions?
