Help - ORDER by RAND() CPU USAGE

[defonic]

Hello All,

 

Having issues with a shared hosting account using to much RAM/CPU on my site

 

Currently using:

SELECT * FROM table ORDER by RAND() LIMIT 3

 

Problem is I have over 200,000 rows. Is there a way to...

(pseudo)

select * from table where id between max(id) and max(id) -50 ?

 

so it only returns the last 50 rows then do a order by rand() limit 3? Does this type of query exsist? and if so will it speed up the query by only selecting rand from last 50 rows?

 

any help appreciated!

[fenway]

In the sticky for this board, there are alternatives.

[defonic]

Thanks, I searched the site but time was not on my side!

 

I figured it out and might as well post the solution for future members.

 

SELECT * FROM table
WHERE id BETWEEN ( SELECT (MAX(id)-50) FROM table) AND ( SELECT (MAX(id)) FROM table)
ORDER BY RAND()
LIMIT 3

 

This will get the last 50 records (assuming there is auto increment on id) then randomly choose 3