Advanced Search

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.

Expand | Embed | Plain Text

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.