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;
            $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;


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.

  • 1
    \$\begingroup\$ 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. \$\endgroup\$
    – Erik
    Mar 6, 2014 at 20:17
  • \$\begingroup\$ 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. \$\endgroup\$ Mar 7, 2014 at 10:33
  • \$\begingroup\$ 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. \$\endgroup\$
    – jsanc623
    Mar 7, 2014 at 15:08
  • \$\begingroup\$ @jsanc623 The times are just for formatting data, the query itself is very simple and fast. See above edit. \$\endgroup\$
    – Erik
    Mar 7, 2014 at 20:42
  • \$\begingroup\$ @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. \$\endgroup\$
    – jsanc623
    Mar 7, 2014 at 21:59

1 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.


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


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.