PHP Array vs MYSQL Queries speed optimization

Question

I'm trying to make my php mysql driven pages as fast as possible. I've got a fairly standard website with a left-column menu with lists of articles and recipes (loaded from the db) and then on the main content there are some randomly changing articles with clipped previews and also random recipes.

What I've got at the moment is one sql query which loads all the articles and recipes into a php array that looks like this:

$s = s("SELECT * FROM `pages` WHERE `visible`='1' ORDER BY `group` ASC, `date` DESC");
   while($r=mysql_fetch_assoc($s)) $hPage[]=$r;
   $hPage_count = count($hPage);
   $hPage_keys = array_keys($hPage);
   $hPage_size = sizeOf($hPage_keys);

Then I simply refer back to and search this array for all the data I need for different parts of my page.

One of my loops looks something like

for ($i=0; $i<$hPage_size; $i++){
$c = $hPage[$hPage_keys[$i]];
if($c['group']==1){
$type[$c['type']][count($type[$c['type']])] = array('title'=>$c['title'],'url'=>$c['url']);
}
}

What I need to find out is, is this the fastest way to do it? With only one SQL query, loading everything into a php array and then working with that, Or would it be better to make multiple SQL queries?

Thanks for any help.

Answer 1

As a rule of thumb, pulling all the information you need in as few database calls as possible is a good thing for application performance. Assuming you aren't pulling down a ton of information you don't actually need for the page I think you're probably already on the right track.

Since you're actually using the database to query, you'll want to make sure the query is well optimized as well.

As with all things performance related only way to really know is with a measurements and a profiler, though, and that only if there's really a need to increase the performance.

Answer 2

A database's sole purpose in life is to collect, organize, crunch, and return data. It's best to let it to what it does best. However, the database is often the biggest bottleneck. Keep the number of queries down and make sure you make each call count.

If you're querying a single table and require more than one query to do the job, there is likely a more efficient and elegant solution.