0

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.

Barranka
  • 20,547
  • 13
  • 65
  • 83
rhill45
  • 559
  • 10
  • 35

1 Answers1

1

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.

duffymo
  • 305,152
  • 44
  • 369
  • 561
  • thank you @duffymo, this is a good place to start. Confused on how to set up the index to handle a randomized function though. Maybe in the relationship between the two tables? – rhill45 Jun 24 '14 at 16:19
  • 1
    I don't think an index is the way to go here. I'd query the values to the middle tier and randomly select there. – duffymo Jun 24 '14 at 17:33