|
|
Posted by Ridge Burner on 04/28/06 23:48
"Chung Leong" <chernyshevsky@hotmail.com> wrote in message
news:1146254836.874212.306620@e56g2000cwe.googlegroups.com...
>
> 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.
>
What if I change it to say something like 'SELECT COUNT(column1) FROM
myTable WHERE live='0' LIMIT 1'
I understand the primary keys may not always return a reliable result, but
I'm questioning whether it is better to use one query to return a row vs.
using 2 queries to return a random row.
Right now we are experiencing issues with MySQL being able to keep up with
all of the traffic. The first SQL statement is used for ad banners, and our
hoster has mentioned that this is not the most efficient way to do what were
doing. The only other way I can see to retrieve a random row is to query for
the # of rows that match the criteria, then use that # to have PHP generate
a random # between 1 & that #, then re-query MySQL for the row that PHP
generated. After that I'll need to make sure that the row does actually
exist in the table. If it doesn't, I have to start again.
It's just to me, the second approach seems to have a lot more overhead in it
than the first.
Navigation:
[Reply to this message]
|