Posted by bobzimuta on 04/29/06 01:35

Ridge Burner wrote:
> Can someone tell me which of these 2 SQL queries will be more efficient? I'm
> having a debate with another guy about which would be less resource
> intensive for MySQL.
>
> The first uses MySQL to pick a random row in a single statement:
> <?php
> $sql = "SELECT FROM myTable ORDER BY RAND() LIMIT 1";
> $query = mysql_query($sql,$conn);
> ?>
>
>
> The second does the same thing, but uses 2 queries to do it:
> <?php
> $sqlA = "SELECT COUNT(id) FROM myTable";
> $queryA = mysql_query($sqlA,$conn);
> $num = mysql_result($queryA,0,0);
> $random = rand(1,$num);
> $sqlB = "SELECT FROM myTable WHERE id='" . $random . "' LIMIT 1";
> ?>
>
> Basically, which approach would be better to use in a high traffic
> environment to retrieve a single random row?
>
>
> Thanks for any help/advice you can give!
>
> ps. the code may not be 100% correct, it is for demonstration purposes only!

MySQL creates an internal counter for the number of rows in a table (at
least MyISAM as far as I know). So doing "SELECT COUNT(*) FROM table"
is instant. So as far as I know, using the COUNT(*) should be faster,
as long as id is a unique (or primary) key. If in doubt, use the
EXPLAIN syntax to see how many rows are being queried for the two
different queries. Hope that helps.

[Back to original message]