Differences

This shows you the differences between two versions of the page.

Link to this comparison view

ressources:mysql:faster_random [2013/06/16 22:51] (current)
Line 1: Line 1:
 +I am running MySQL version 5.0
 +<​code>​mysql ​ Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (x86_64) using readline 5.2</​code>​
 +
 +
 +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
 +
 +<code sql>​select page_namespace,​page_id,​page_title ​
 +from page 
 +where 1 order by RAND() limit 1;</​code>​
 +
 +With a little search, I found this syntax that significatively reduces my query time.
 +<code sql>​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;</​code>​
 +
 +
  
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