How can I optimize this database to array structure code?

Question

I need to take data from a MySQL database and message it into a format expected by the front end of an application - I can not change the front end as other services provide it data in this same format.

The database is structured as follows:

id type value label optgroup
1 car ix5 Ford Taurus Ford
2 car ix6 Ford Focus Ford
3 car ix9 Cobalt Chevy
4 planet ix8 Earth Default

The output from this code must do the following: for types with optgroups, records must be categorized by optgroup; if there is only one optgroup, then it should be ignored. The real data has hundreds to thousands of rows per type. The finally array output from this data would be:

$data = [
 'car' => [
 'chevy' => [ 'ix9' => 'Cobalt' ],
 'ford' => [ 'ix5' => 'Ford Taurus', 'ix6' => 'Ford Focus' ]
 ],
 'planet' => [ 'ix8' => 'earth' ]
];

The code I have doing this currently works, but is a bit slow, and I am looking for a possible improvement. Here's the functioning code, where $STH->result() is the database result as an array of rows:

protected function _format($STH)
{
 $data = [];
 foreach ($STH->result() as $row)
 {
 if ( ! $row->optgroup)
 $data[ $row->type ][ $row->value ] = $row->label;
 else
 $data[ $row->type ][ $row->optgroup ][ $row->value ] = $row->label;
 }
 // selects with a single optgroup can have that optgroup removed
 foreach ($data as $menutype => $optorkey)
 { 
 if (is_array($optorkey) && count($optorkey) == 1)
 $data[$menutype] = current($optorkey);
 }
 return $data;
}

EDIT

The original query generating the data is very simple, as follows:

SELECT type, value, label, optgroup FROM ####.options ORDER BY type, optgroup, label ASC

The data in this table is updated frequently by automated processes.

• Erik: slow just means it could be faster; for the current dataset it runs for about .75 seconds but it runs on EVERY page load. Currently I'm caching the results for 30 seconds but honestly web pages shouldn't take .75 in total to generate, much less for a few dozen menus -- maybe there's no a faster way to do it, but I thought here would be the place to ask.
• Barry Hennessy: I'd be interested to know how big your table is/is supposed to be. Also are your times there just for the formatting of the data or does it include the query? It'd be good to know if the operation is spending time streaming data off disk or processing it in php.
• jsanc623: Can you post the SQL query you are using? That would better allow us to determine if PHP is at fault or if an intricate SQL query is at fault.
• Erik: @jsanc623 The times are just for formatting data, the query itself is very simple and fast. See above edit.
• jsanc623: @Erik - thanks! Just wanted to make sure that wasn't a cause. Are you able to do a top on the machine running the script while the script is running and see if PHP bubbles in memory or CPU usage? If not, use us2.php.net/memory_get_usage to get the memory usage of the script.

Answer 1

Non-optimizing suggestions

Your SQL is not very clear. What kind of object is acceptable for the type column, and how do you manage to put items into that column? How specific or general should the terms be in that column? What is the value column? label, and even optgroup? To me, these are extremely confusing. Is optgroup a group of options, or a group of outputs? This isn't official, but it's a good read and it might help you.

If each value of value (naming confusion right there!) starts with "ix", then why is it there? I can't even tell what that column means because the Earth comes between two cars! Anyways, if each entry starts with that prefix, consider adding it in your PHP.

Optimizing...

I don't see to much you can do. Your query is already quite minimal, but I'm not an expert there.