MySQL Forums
Forum List  »  MyISAM

order by rand() Query takes too long (writes to disk)
Posted by: Bernd Meier
Date: October 28, 2012 02:01PM


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

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.

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

Thank you very much in advance

Options: ReplyQuote

Written By
order by rand() Query takes too long (writes to disk)
October 28, 2012 02:01PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.