My SQL script scans ~7000 records and retrieves 100 of them randomly, also pulls correlative data from an additional table (via SQL JOIN). My server response time is extremely slow using this code (>7 seconds).
ROUND(AVG(r.rank),0) AS avrank,
COUNT(r.rank) AS countrank
FROM stories s
LEFT JOIN ratings
AS r
ON r.storyidr = s.id
GROUP BY s.id
ORDER BY RAND()
LIMIT 100";
I was wondering if someone could make a recommendation on something I could do to improve my response time, and increase page speed. Thank you in advance.
You should run EXPLAIN PLAN on this query. I'm guessing that all those random operations force you to scan each and every row. How could it be otherwise?
If you see TABLE SCAN, you'll either have to rewrite the query to eliminate the page scans.
Indexes are your friend.
I would recommend creating a VIEW on all the rows in the JOIN.
7000 records isn't a large number. I'd do the query on all the VIEW data and pull out a random sample on the server side.