Re: MySQL Speed


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.

.

Relevant Pages

  • Re: MySQL Speed
    ... The first uses MySQL to pick a random row in a single statement: ... The problem here is that the second query is wrong for what you're ...
    (comp.lang.php)
  • Re: MySQL Speed
    ... The first uses MySQL to pick a random row in a single statement: ... The problem here is that the second query is wrong for what you're ...
    (comp.lang.php)
  • Re: php vs mysql speed
    ... (PHP: Loop through results to find Bob and Dave.) ... Both the first and the third queries can be described as "one big query". ... It was bugging me because php is obviously faster than mysql so I ...
    (comp.lang.php)
  • PHPs MySQL Query Length Limit? (Long SQL failing!)
    ... I've been doing PHP for a pretty good while now, ... Then I go and use it with a mysql query. ... The query is written correctly AKAIK, but no data reaches the table I'm ... The only difference between queries that work and those that do not ...
    (php.general)
  • Re: php vs mysql speed
    ... (PHP: Loop through results to find Bob and Dave.) ... Both the first and the third queries can be described as "one big query". ... It was bugging me because php is obviously faster than mysql so I ...
    (comp.lang.php)