0

I have this script to get Posts:

$totalrows = 60;

$sql = "SELECT 
 posts.Tags as tags, 
 posts.OwnerUserId as postsid, 
 posts.Id as postid, 
 posts.Body as body, 
 posts.Title as title, 
 users.Id as userid, 
 users.DisplayName as usersname  
FROM posts 
JOIN users ON posts.OwnerUserId = users.Id 
JOIN (select posts.id from posts where posts.title != '' order by rand() asc limit " . $totalrows .") AS tmp_result
ON (posts.Id = tmp_result.Id)";


$r = mysql_query($sql) or die(mysql_error());

The problem is the server is freezing and require restarting, the mysql file is very large. What causing freezing the server? What can I do to optimize the above query?

markus
  • 40,136
  • 23
  • 97
  • 142
Dan
  • 21
  • 3
  • 1
    I feel special when you say sir. Have you checked the indexes of the table and so forth? Are you attempting to read the whole table at once? Also, ORDER BY RAND() is very inefficient, and I highly recommend not using it. –  Jan 15 '11 at 07:02
  • Hello Kevin, how can I change the script then? – Dan Jan 15 '11 at 07:08
  • I'm editing and retagging this question, since it's 95% SQL. Consider removing the PHP bits since they almost entirely irrelevant to the question, and presenting it as a SQL question instead. – Yi Jiang Jan 15 '11 at 08:16
  • Is there anything like Oracle HINTS in MYSQL??? Normally we use HINTs to optimize the queries specifying indexes and search logic etc. – Rajeev Jan 15 '11 at 09:18

3 Answers3

0

Order by rand() is really expensive, so you might want to consider doing something like pickint the id's beforehand in code, and asking for those particular ones.

Furthermore, use an "EXPLAIN" on the query, and see what happens. If i'm not reading this wrong, you'll probably see the subquery in there, and you can't use an index on that (because it's a 'new' table, so it doesn't have an index.

As a last point, check the index(es?) of your tables.

Nanne
  • 64,065
  • 16
  • 119
  • 163
0

A simplification - your self-join on posts could be

JOIN posts AS p2 ON posts.id = p2.id AND p2.title != '' (should that be p2.title IS NOT NULL?)

dkretz
  • 37,399
  • 13
  • 80
  • 138
0

ORDER BY RAND() does not scale.

The answer to mysql-alternatives-to-order-by-rand provides a smart way of returning rows in random order.

Also, try adding LIMIT 100 at the end of your query to see if it helps.

Community
  • 1
  • 1
MattBianco
  • 1,501
  • 2
  • 20
  • 30