Order By rand() Reduce Time


/ Published in: MySQL
Save to your folder(s)

A guy asked me to reduce the query execution time. He is getting 10 results but the query had joins which makes it time consuming.


Copy this code and paste it in your HTML
  1. Original Query
  2.  
  3. $totalrows = 10;
  4.  
  5. $sql = "SELECT
  6. posts.Tags as tags,
  7. posts.OwnerUserId as postsid,
  8. posts.Id as postid,
  9. posts.Body as body,
  10. posts.Title as title,
  11. users.Id as userid,
  12. users.DisplayName as usersname
  13. FROM posts
  14. JOIN users ON posts.OwnerUserId = users.Id
  15. WHERE posts.Title != '' order by rand() asc limit " . $totalrows;
  16.  
  17. $r = mysql_query($sql) or die(mysql_error());
  18.  
  19.  
  20.  
  21. Modified Version
  22.  
  23. $totalrows = 10;
  24.  
  25. $sql = "SELECT
  26. posts.Tags as tags,
  27. posts.OwnerUserId as postsid,
  28. posts.Id as postid,
  29. posts.Body as body,
  30. posts.Title as title,
  31. users.Id as userid,
  32. users.DisplayName as usersname
  33. FROM posts
  34. JOIN users ON posts.OwnerUserId = users.Id
  35. JOIN (select posts.id from posts where posts.title != '' order by rand() asc limit " . $totalrows .") AS tmp_result
  36. ON (posts.Id = tmp_result.Id)";
  37.  
  38.  
  39. $r = mysql_query($sql) or die(mysql_error());

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.