MySQL Bugs: #45536: ORDER BY RANDOM HANGS UP QUERRIES

Bug #45536	ORDER BY RANDOM HANGS UP QUERRIES
Submitted:	16 Jun 2009 18:02	Modified:	18 Jun 2009 8:44
Reporter:	Bugs System	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S3 (Non-critical)
Version:	5.0.67 5.1.31 5.1.35	OS:	Solaris (Solais 10 u6)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	copy to tmp table, order by, rand, tmp table

Description:
When executing a query of this type:

select (...) ORDER BY RAND();

execution time for 1 query: approximatively 1 second.

When executing 10 times the same (with different connections): 22s.

Analyzing MySQL behaviour, it seems that:

- launching the same query in new processes causes the first ones to be longer
(if the last "ORDER BY RAND()" query is not finished, all the others stay in a "copy to tmp table" state)

- modifying the query with a simple "ORDER BY table.id" (indexing table.id) behaves really better

I THINK something is locking (seems to be the RAND() ) and prevents the querries to be executed.

How to repeat:
Execute parallels queries containing an "ORDER BY RAND()" operation.
Launch new parralels queries before the first ones finish.

Thank you for the report.

You said "if the last "ORDER BY RAND()" query is not finished, all the others stay in a "copy to
tmp table" state" This is correct: ORDER BY RAND() uses filesort and can not behave as fast as ORDER BY some_key.

This happens, because when you use ORDER BY RAND() is same if you write query like:

SELECT id, rand() FROM t1 ORDER BY RAND();

This means rand() will be created for every row in the table.

So this is not a bug.

I think you didn't understand what I meant. Maybe I was not clear enough.

The problem is not that the ORDER BY RAND() takes a long time. I know it's normal. By the way, the problem is the same if you order on an existing column or anything that takes a little time.

We have two kind of servers here. Linux and solaris 10. The problem appears only on solaris. Note : on linux, mysql forks chield where solaris uses threads, maybe it's related..

The request with which we encountered the problem is this one (it's one from wordpress) :

SELECT t.*, tt.*
FROM wp_terms AS t
INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id
INNER JOIN wp_term_relationships AS tr ON tt.term_taxonomy_id = tr.term_taxonomy_id
INNER JOIN wp_posts AS p ON tr.object_id = p.ID
WHERE tt.taxonomy IN ( 'post_tag' ) AND p.post_date_gmt < '2009-06-18 13:53:01' AND tt.count > 0
GROUP BY t.term_id
ORDER BY RAND() LIMIT 20;

Here is the point that is the bug :

This query alone takes around 1.5 seconds to resolve on solaris (on linux it takes 0.5 seconds - if usefull). It's quite long, but this doesn't matter...
The real problem is that if you launch another similar query before the first one ends, the first will be delayed. If you launch a third one, the two firsts will be delayed and so on, and it can last like this forever (meaning until server crashs). Queries are lock in "copy to temp table" state, but nothing is written in tmpdir.... Files are created, but not filled.

To reproduce the bug, you need to be on solaris 10 u6 (we've tested with many different my.cnf with no changes). Use the query above on a medium-sized database (the one we have is around 55Mo, including indexes), so that it takes more than one second to resolve, then make a little loop to launch a new query each second

Thank you for the feedback.

Tested both on Solaris and Linux. Same problem is repeatable on Linux as well, but as query runs on Linux faster (in my case too) it is harder to notice.

But this problem does not apply to ORDER BY RAND() only and was reported earlier: see bug #31551 and bug #36448. So this is duplicate of bug #31551