MySQL question- random entry

[Qun Cao]

Hi,

I have a question about sql query, I want to access a random entry in
table A. It should be a simple task, what I need is to first do
"select count(*) from A" to get the total, then generate a random
number N from the total count. The problem is, now I need to access
entry no.N of table A, yet A doesn't have a counting field; so I cannot
execute something like "select * from A where id = N". Does anybody
know how to access a certain entry from its' sequential order in the
table?

Thanks a lot!
qun

[Geoff Berrow]

Re: MySQL question- random entry

Message-ID: <1131404016.356 228.220800@f14g 2000cwb.googleg roups.com> from
Qun Cao contained the following:
[color=blue]
>Does anybody
>know how to access a certain entry from its' sequential order in the
>table?[/color]



SELECT * FROM table LIMIT $n,1


--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[Qun Cao]

Re: MySQL question- random entry

thanks, Geoff.
Actually I just found this rather convenient sql command:
select * from table order by rand() limit 1;
returns one random entry from the table, exactly what i needed! :)

qun

[Qun Cao]

Re: MySQL question- random entry

thanks, Geoff.
Actually I just found this rather convenient sql command:
select * from table order by rand() limit 1;
returns one random entry from the table, exactly what i needed! :)

qun

[Bill Karwin]

Re: MySQL question- random entry

Qun Cao wrote:[color=blue]
> select * from table order by rand() limit 1;
> returns one random entry from the table, exactly what i needed! :)[/color]

Be careful if your table contains a large number of rows.

Sorting by a function cannot benefit from any indexes defined on the
table. So the performance is as if you had no indexes, and for a large
table, this can cause bad performance to your PHP application.

You should try that command first and see how long it takes when applied
to your data (and also consider how much your table is expected to grow
in the future).

SELECT COUNT(*) FROM table does benefit from a unique index or primary
key on the table, but even that can be slow for a large table, or if the
index isn't cached.

You can maintain a table that notes the count of records, or you can use
SHOW TABLE STATUS to get an approximate count of records in a table.
See discussion of COUNT() in this page:
http://dev.mysql.com/doc/refman/5.0/...trictions.html

Regards,
Bill K.