|
|
Posted by Chung Leong on 04/28/06 23:07
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?
The problem here is that the second query is wrong for what you're
trying to do. The number of rows in a table doesn't necessarily have
anything kind of relationship with the primary keys. Rows could have
been deleted or the seed value of the auto-increment column might not
have been 1.
Navigation:
[Reply to this message]
|