Mysql vs php: Get rank by scores for a user having only scores and a user id. Which method is the fastest Php or Mysql?(Drupal5)

Posted by: 
Dominique De Cooman
On: 
Tuesday, April 29, 2008 - 23:02

I have a Mysql table with 2 rows. Total which is the score and uid which is the user id. I want to know which user has what rank. What's the fastest? Let Mysql do the whole sorting job or let Php do it. So I did an experiment. And the results are:

Php did the job in : 0.00024099999999994s
Mysql did the job in : 0.00073499999999993s

Php wins significant!

Here is the code I used to test this:
I used the microtime() to measure the time interval.

//get position PHP
    $time1 = microtime();
    $rankings = db_query("SELECT uid FROM {pronostiek_total} WHERE ORDER by total");
    $ranks = array();
    while ($rank = db_fetch_object($rankings)) {
      $ranks[] = $rank->uid;
    }
    $position = array_search($account->uid, $ranks);
    $output .= '<div>'.t('Holds position : ').($position + 1).'</div>';
    $time2 = microtime();
    $intervalphp = $time2 - $time1;
    $output .= 'Php:'.$intervalphp;
 
    //get postion MYSQL
    $time3 = microtime();
    db_query("SET @rownum = 0");
    $position = db_result(db_query("SELECT rank FROM (SELECT @rownum:=@rownum+1 AS rank, uid FROM {pronostiek_total} pt ORDER BY pt.total ASC) as t1 WHERE t1.uid= %d", $account->uid));
    $output .= '<div>'.t('Holds position : ').$position.'</div>';
    $time4 = microtime();
    $intervalmysql = $time3 - $time4;
    $output .= 'Mysql:'.$intervalmysql;
 
   return $output;

Why does php win easly?
When you do a subquery the main query gets called every time for each result row. So unless your result table from the main query is small you shouldnt use subqueries. Php will take more resources but the job will be done much faster.

Add new comment