I am running MySQL version 5.0

mysql  Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (x86_64) using readline 5.2

On my large database (more than one million records) i wanted to pick a random article and this was amazingly slow, of course I was using the standard syntax

SELECT page_namespace,page_id,page_title 
FROM page 
WHERE 1 ORDER BY RAND() LIMIT 1;

With a little search, I found this syntax that significatively reduces my query time.

SELECT page_namespace,page_id,page_title 
FROM page T 
JOIN (SELECT CEIL(MAX(page_id)*RAND()) AS ID FROM page) AS x 
ON T.page_id >= x.ID LIMIT 1;
ressources/mysql/faster_random.txt · Last modified: 2013/06/16 22:51 (external edit)
 
 
Creative Commons License Powered by PHP Valid XHTML 1.0 April