order by rand() Query takes too long (writes to disk)

[Bernd Meier]

HI

I got a MYISAM table in my database with about 7 million rows.

table has index,url,external_link_count fields.

Now I want a seachquery to get one url of each domain.Each query should return different urls than the last query.

so I do:
SELECT x.* FROM (SELECT * FROM table WHERE external_link_count < 50 order by rand()) x GROUP BY SUBSTRING_INDEX(url,'/',3)

Now my problem is:
1) The query locks the database and the same query from other tools can not access the datbase -> huge delay time
All other tools has to wait for the current query to complete.

2) The query writes a "temp" table to disk -> huge delay time

Question:
Is it possible to do a smarter and faster query to solve this problem, or is there any idea to tweak the performance?

The running tools execute this query 4-8 times and hours in total.

System:
I5 Core 2,7Ghz
16 GB Ram
120 GB SSD Disk
Windows 7 Enterprise 64Bit


Thank you very much in advance