Locking -- switch to InnoDB.
ORDER BY RAND() reads the entire table -- hence, the slowness.
> Is it possible to do a smarter and faster query to solve this problem, or is there any idea to tweak the performance?
Such a deal I have for you!
http://mysql.rjweb.org/doc.php/random
This would also help:
SELECT x.*
FROM
( SELECT *
FROM table
WHERE external_link_count < 50
order by rand()) x
GROUP BY SUBSTRING_INDEX(url,'/',3) ;
-->
SELECT y.*
FROM
( SELECT id
FROM table
WHERE external_link_count < 50
order by rand()) x
JOIN table y ON y.id = x.id
GROUP BY SUBSTRING_INDEX(y.url,'/',3);
That is, have the subquery find only the PRIMARY KEY, not the whole row.
For further discussion, please provide SHOW CREATE TABLE