No more machine gunning - Use Underscore.php

So - a lot of caveats for this post. This is not overly intense benchmarking. I am trying to stay "close to the bone" - there is no output here, I'm not using models or other "correct" code practices. My aim is just to show you that for most cases you encounter a join with the Underscore library will be faster than using a foreach loop.

So first of all, what am I talking about? You've all run into cases where you might have, say, a table of models & another of specific items of each model type. You would like to show a nested output such as:
Model ABC
--- Item 1
--- Item 2

Model BCD
-- Item 3
-- Item 4

and so on. Problem is, when you run a normal join,
SELECT * FROM model m JOIN item i ON (m.id = i.model_id)
you get:
Model ABC --- Item 1
Model ABC --- Item 2
Model BCD --- Item 3
Model BCD --- Item 4
and now you have to parse the foreach loop for each time the model changes, to set up the page the way you want it.

So instead, you come up with the idea of a foreach loop over your model results, and then calling the db on each one to get the items. Something like:
$sql = 'SELECT * FROM model m';
$query = $this->db->query($sql)->result_array();

foreach($query as $model)
{
	$sql = 'SELECT * FROM item i WHERE model_id = '.$model['id'];
	$inner = $this->db->query($sql)->result_array();			
}
And then you tell yourself, "It's ok - mysql is really fast with connections" and you try to ignore it, but all day long you have that same feeling you had when you ran over the kitten and drove off without saying anything.

Why? Well, if you pull https://github.com/jrmadsen67/codeigniter_underscore and set up in a CI install (or rework it in straight php - I've included the db data for your convenience) you'll see. The data has 5000 models & 13,867 items - enough to see some real numbers with.

Running /welcome/test_1 - the first query with the simple join - we get total execution times of around 0.1821 (this is done with the CI profiler and is including the CI loading as well).

Looking at /welcome/test_2 - the foreach loop - that shoots all the way up to 1.2620

More than 600% execution time!

Got your attention? I thought so.

"Ok, great, Jeff. I'll use a join. So now I'm back to square one. How do get my data properly formatted?"

Enter Underscore.php. This php port of the absolutely marvelous js library by the same name has a whole host of great functions for searching, ordering & manipulating arrays. My goal is to start demonstrating a few of the ways you can use it for result arrays. The first will be the groupBy function (http://brianhaveri.github.com/Underscore.php/#groupBy)
$this->output->enable_profiler(TRUE);

require_once(APPPATH.'libraries/underscore.php');

$sql = 'SELECT *, m.name AS model_name FROM model m JOIN item i ON (m.id = i.model_id)';
$query = $this->db->query($sql)->result_array();

$grouped_result = __()->groupBy($query, 'model_name');

//var_dump($grouped_result);
We get the full result set, group by the model name, and Voila! as the Belgians say. Uncomment the var_dump() and you'll see your results are organized exactly as you want them. Total execution time? About 0.2968 - twice as long as the join alone, but still 25% of the foreach method. Furthermore, it was able to handle a rather hefty result set, not just a few hundred rows.

Hope that convinced you! I plan to dig further into the Underscore library and show some other use cases where I think it will be helpful.

Contact me