in_array performance vs query performance

[thefortrees]

Hi all,

 

Just wondering what you think is more efficient in this situation.

 

I have three tables: items, promocodes, and items_promocodes.

items_promocodes is (obviously) a relational table for items and promocodes.

 

If a promocode is applied to any number of items, each item is stored as a new row in items_promocodes with the related promocode as a field.

 

I want to pull all items from the db and check to see if a promocode has been applied to each one.

 

Which one of these is more efficient?

 

$query_allItems = "select items_id from items";
$res_allItems = mysql_query($query_allItems);

while ($row_allItems = mysql_fetch_row($res_allItems) {
     $query_checkItem = "select items_promocode_id from items_promocode where item = '{$row_allItems[0]}'";
     $res_checkItem = mysql_query($query_checkItem);
     
     if (mysql_num_rows($res_checkItem) > 0)
          // Item has a promocode applied to it.
}

 

$allItemsPromocodes = array();

$query_allItemsPromocodes = "select items_id from items_promocodes";
$res_allItemsPromocodes = mysql_query($query_allItemsPromocodes);

while ($row_allItemsPromocodes = mysql_fetch_row($res_allItemsPromocodes)) {
     $allItemsPromocodes[] = $row_allItemsPromocodes[0];
}

$query_allItems = "select items_id from items";
$res_allItems = mysql_query($query_allItems);

while ($row_allItems = mysql_fetch_row($res_allItems)) {
     if (in_array($row_allItems[0], $allItemsPromocodes))
          // Item has a promocode applied to it
}

 

Is it more efficient to traverse an (possibly enormous) array or to query a database (possibly thousands) of times?

I would guess that the query would be more efficient.

 

[rbrown]

What you should do is create a stored prodcedure and if you can't...

 

Create a table with the only the data you want and set a cron to run the script to update the table every hour or whenever.

 

or even better...

 

Make the update the table script run after every processed sale... Like when they get sent to the thank you page.

 

That way the server doesn't get pounded as much.

And you are still getting the data "real time".