Posted By

aamirrajpoot on 01/10/11


Tagged

rand


Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

MicronXD


Order By rand() Reduce Time


 / Published in: MySQL
 

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.

  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  

You need to login to post a comment.