I need some slow Query advice. Below are 3 queries that are running real slow compared to the rest. What can I do to make these run faster?
* 0.0565 seconds - LINE 29: SELECT lid from cms_poems_poems WHERE allowcomments != 3 and poemsratingsummary != 0 AND submitter !='$username' and totalvotes >= 6 and NOT EXISTS (SELECT v.ratinglid FROM cms_poems_votedata As v WHERE lid = v.ratinglid AND ratinguser='$username') ORDER BY RAND()
* 0.7001 seconds - LINE 1324: SELECT lid, title, jgroup, description, date, hits, poemsratingsummary, totalvotes, totalcomments, name, cid, allowcomments from cms_poems_poems ORDER BY RAND() LIMIT 1
* 0.2995 seconds - LINE 51: SELECT p.title, p.lid, p.submitter FROM cms_poems_poems as p where submitter !='$username' and p.allowcomments != 3 AND NOT EXISTS (SELECT v.ratinglid FROM cms_poems_votedata As v WHERE p.lid = v.ratinglid AND ratinguser='$username') ORDER BY RAND() LIMIT 1
table: cms_poems_poems - Rowcount 10,821
primary - lid
index - sid
index - cid
index - date, jgroup
index - hits, jgroup
index - name
The ORDER BY RAND() is going to be the slow part. Consider looking at one of the Coppermine random blocks and see how it's done there. It just gets MySQL to select a whole bunch of lines, then has PHP randomize them instead of MySQL.