Mysql rand

[agriz]

Hi,

I have a table with 2 laksh records.
If i use rand() in mysql query it takes approximately 1.5 seconds to run

So i use mt_rand(1, 200000)

It returns most of the times results in 1,000,00s

The query is like this.

$query = "SELECT * FROM tbl WHERE Length(word) > 6 AND id >= $id";

Which brings almost same result every time.
The table is english words table. I want to get words more than 6 letter

How to write better query with almost best random fetch.?

[smantscheff]

select * from tbl order by rand() limit 1;

[agriz]

select * from tbl order by rand() limit 1;

Yes, this one takes more seconds.

[agriz]

select * from tbl order by rand() limit 1;

http://www.webtrenches.com/post.cfm/avoid-rand-in-mysql

[smantscheff]

If you can guarantee an uninterrupted sequence of id numbers, you could select one of those with the rand function:

select max(id) from tbl into @p;
select * from tbl where id=ceil(rand()*@p);