Re: MySQL Speed
- From: "Chung Leong" <chernyshevsky@xxxxxxxxxxx>
- Date: 28 Apr 2006 13:07:17 -0700
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.
.
- Follow-Ups:
- Re: MySQL Speed
- From: Ridge Burner
- Re: MySQL Speed
- References:
- MySQL Speed
- From: Ridge Burner
- MySQL Speed
- Prev by Date: MySQL Speed
- Next by Date: Re: MySQL Speed
- Previous by thread: MySQL Speed
- Next by thread: Re: MySQL Speed
- Index(es):
Relevant Pages
|
