Re: MySQL Speed


"Chung Leong" <chernyshevsky@xxxxxxxxxxx> wrote in message
news:1146254836.874212.306620@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

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.





















.

Relevant Pages

  • newbie :-sending variable value between two scripts...
    ... I am having some problems with my PHP and MySQL, basically, I need to ... #Enter Survey Author... ... #execute query ...
    (php.general)
  • 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: Dynamic form generation for editing mysql data
    ... I hope this is the right place to post my query. ... to php and MySQL (but have been programming in C++/python for a few ... regarding editing MySQL data using a form. ... the format of the data in the database. ...
    (comp.lang.php)
  • Re: Reducing load for LAMP app?
    ... Not from PHP, there isn't. ... Just like every program running in a system is considered independent. ... the MySQL query cache may be more efficient. ...
    (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)