Jump to content


Photo

PHP Loop or Mysql Query


  • Please log in to reply
2 replies to this topic

#1 BrandonKahre

BrandonKahre
  • Members
  • Pip
  • Newbie
  • 6 posts

Posted 30 March 2006 - 05:52 PM

I have a function (2 variations):
function designer_lookup($designer_code) {
    $select = "SELECT `designer_text` FROM `designer_lookup` WHERE `designer_code` = '".$designer_code."' LIMIT 1";
    if ($result = @mysql_query($select)) {
        $fetch = mysql_fetch_array($result);
        return $fetch['designer_text'];
    } else {
        return (boolean)false;
    }
}

function designer_lookup($designer_code, $designer_lookup_fetch) {
    for ($i=0; $i<count($designer_lookup_fetch); $i++) {
        if (strpos($designer_lookup_fetch[$i]['designer_code'], $designer_code) !== false) {
            return $designer_lookup_fetch[$i]['designer_text'];
        }
    }
    return (boolean)false;
}

The first one obviously looks up the designer_text by the designer_code by making a mysql query and returning exactly what's needed. However, I can use this function 30+ times in a page. My question is, which function would work fastest?

Notes:
designer_lookup_fetch is a 3d array holding all of the rows and only the two colums (designer_code, designer_text).
the designer_lookup table that I query is currently small, and isn't expected to ever rise above 30 rows.
Also, I have two other functions that are mirrors of this (querying different tables), but they aren't used nearly as often.

Does anyone have any feedback on which function would preform better? Or maybe even another idea completely? My thought is that the first function would obviously become faster (relatively) as the table became bigger.

#2 ober

ober
  • Staff Alumni
  • Advanced Member
  • 5,337 posts
  • LocationEast Coast, USA

Posted 30 March 2006 - 06:40 PM

If you're running PHP 5, you can test it with the following code:
<?php
$time_start = microtime(true);
// your function and calls go here
$time_end = microtime(true);
$time = $time_end - $time_start;

echo "Ran Analysis in $time seconds<br/><br/>";
?>

There is a slight different way to do it if you're running PHP4. Use the following function instead of microtime():
<?php
function microtime_float()
{
   list($usec, $sec) = explode(" ", microtime());
   return ((float)$usec + (float)$sec);
}
?>

Either way, I think the first one is going to be WAY faster.


Info: PHP Manual


#3 BrandonKahre

BrandonKahre
  • Members
  • Pip
  • Newbie
  • 6 posts

Posted 30 March 2006 - 08:51 PM

Thanks for the tip, here's the results I got back:
Now I'm never done a real time test like this before, so I did my best to keep the two conditions as equal as possible.
  • select first 40 rows of the table
  • for each row, fetch
  • for each fetch, lookup the designer based on the two methods described above
  • run the fetch through a function that echos out the information in a given manner (identical process for both tests)
for 40 rows:
using queries: Ran Analysis in 0.011135 seconds, 0.011551 seconds, 0.01166 seconds
using an array: Ran Analysis in 0.007887 seconds, 0.007912 seconds, 0.007984 seconds

then I decided to add criteria for the products (in first test there were only 3 designers):
this time the query pulled in 5 designers:
using queries: Ran Analysis in 0.011026 seconds, 0.011846 seconds, 0.012299 seconds
using an array: Ran Analysis in 0.008007 seconds, 0.008027 seconds, 0.007976 seconds


still not satisfied, I had it dump the whole products database (191 rows):
using queries: Ran Analysis in 0.51082 seconds, 0.53646 seconds, 0.436927 seconds
using an array: Ran Analysis in 0.312473 seconds, 0.316537 seconds, 0.398623 seconds

I guess that really answers my question. Let me know if I'm skipping something in my testing enviornment.

Thanks




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users