Report Page on Volunteer Signup Website

Question

I am creating a website that collects volunteer registrations for events (also called races in the code). I am using PHP CodeIgniter (which follows the MVC model). I created a control panel that has lots of tools and reports.

Right now, my models generate EVERYTHING that is needed for that model to be complete. That process runs more SQL queries than technically needed for most pages.

I am worried that my website is running too many SQL queries.

I have chosen one particularly intensive page to show as an example here. To generate the report screenshotted below, the website is running 375 SQL queries, many of them complex INNER JOINS. As my website grows in size, I am worried that page load times will become too high.

I am open to all suggestions for improving the code. In particular, I would like to know if I can optimize page load time without disorganizing the code too much.

Some specific questions I would like your opinion on are:

• I assume that SQL queries are going to be my performance bottleneck, rather than PHP code? So in general, it is good to keep the number of SQL queries down? (My SQL database is on a different server.)
  • How important is it to follow the DRY principle and other principles and keep everything organized, versus keeping the code fast?
• Possible Solutions
  • Do you think the current system is fine? Perhaps I am freaking out? Even with all those SQL queries, the page load time is only 0.5215 seconds.
  • Is moving intense pages into their own methods in the model a good way to optimize this? For example, I did it with this page by using get_races_by_company_for_company_report_page() instead of the usual get_races_by_company().
  • Do you think introducing some caching might help to optimize this? That is, creating cache fields in the SQL database, and then updating them on model adds/edits/deletes?
  • Should I consider refactoring my models to take a list of the variables needed as a parameter? For example, make a method called get_races_by_company($list_of_fields_needed), and then before declaring each variable, run something like if ( in_array('race_percent_of_slots_filled', 'list_of_fields_needed', ) ).

Controller

public function list_of_events($company_id_and_text = NULL)
{
    $this->data = set_page_title('Control Panel', $this->data);

    $this->data = $this->_get_company_data_or_throw_error_page($company_id_and_text, $this->data, '/managers/list_of_events/');

    $list_of_races = $this->race_model->get_races_by_company_for_company_report_page($this->data['company']['company_id']);

    $this->data['list_of_years'] = group_races_by_year($list_of_races);

    load_page_with_all_navs($this, 'managers/list_of_events', $this->data);
}

private function _get_company_data_or_throw_error_page($company_id_and_text, $data, $path)
{
    $company_id = extract_number_from_uri($company_id_and_text);
    $data['company'] = $this->company_model->get_company($company_id) OR load_404_page_then_die($this, $this->data);

    $data = add_to_page_title($data['company']['company_name'], $data);

    if ( $data['company']['company_uri'] != $company_id_and_text )
    {
        redirect_and_die($path . $data['company']['company_uri']);
    }

    $this->_authenticate_company($data['company']['company_id']) OR load_403_page_then_die($this);

    return $data;
}

models/Race_model.php

public function get_races_by_company_for_company_report_page($company_id)
{
    $data = $this->race_model->get_races_by_company($company_id);

    foreach ( $data as $key => $race )
    {
        $data[$key] = $this->_add_extra_company_report_fields($race);
    }

    return $data;
}

public function get_races_by_company($company_id)
{
    // SELECT * FROM `races` WHERE `company_id` = X ORDER BY `race_date` DESC
    $this->db->where('race_deleted', 0);
    $this->db->where('company_id', $company_id);
    $this->db->order_by('race_date', 'DESC');
    $query = $this->db->get('races');
    $data = $query->result_array();

    // TODO: test NULL result, make sure it doesn't generate any errors

    foreach ( $data as $key => $race )
    {
        $data[$key] = $this->_add_extra_race_fields($race);
    }

    return $data;
}

private function _add_extra_race_fields($race)
{
    if ( $race )
    {
        if ( !isset($race['race_preview']) )
        {
            $race['race_preview'] = FALSE;
        }

        $race['race_attrition_rate'] /= 100;

        $race = $this->_add_extra_url_fields($race);

        $race = $this->_add_extra_description_fields($race);

        $race['race_date_pretty'] = date('D M j, Y' , strtotime($race['race_date']));

        // TODO: Extremely expensive. Get rid of this, and figure out a way to get list_of_open_shifts, race_is_full, and race_has_no_shifts without getting a list of all shifts with extra data
        $race['list_of_shifts'] = $this->shift_model->get_race_shifts_with_extra_data($race['race_id']);

        // Using a helper function instead of the model function. Saves us a DB query.
        $race['list_of_open_shifts'] = get_open_shift_ids($race['list_of_shifts']);

        $race['race_is_full'] = empty($race['list_of_open_shifts']) ? TRUE : FALSE;

        $race['race_has_no_shifts'] = empty($race['list_of_shifts']) ? TRUE : FALSE;

        $race['race_is_old'] = ( strtotime($race['race_date']) > time() ) ? false : true;

        $race['weeks_until_race'] = calculate_weeks_until_date($race['race_date']);

        $weeks_until_race_zero_decimals = floor($race['weeks_until_race']);
        $weeks_until_race_zero_decimals = $weeks_until_race_zero_decimals >= 0 ? $weeks_until_race_zero_decimals : 0;
        $race['race_target_percent'] = get_target_percent($weeks_until_race_zero_decimals);

        if ( !$race['race_open_to_public'] )
        {
            $race['race_status'] = 'invisible';
        }
        elseif ( $race['race_external_url'] )
        {
            $race['race_status'] = 'redirect';
        }
        elseif ( $race['race_has_no_shifts'] || $race['race_is_old'] )
        {
            $race['race_status'] = 'locked';
        }
        else
        {
            $race['race_status'] = 'ready';
        }
    }

    return $race;
}

private function _add_extra_company_report_fields($race)
{
    if ( $race )
    {
        // If race is linking to an external website, display all blanks.
        // Saves both SQL queries and clutter in the report.
        if ( $race['race_external_url'] )
        {
            $race['race_one_week_delta'] = '';
            $race['race_one_day_delta'] = '';
            $race['race_todays_delta'] = '';
            $race['race_slots_on_website'] = '';
            $race['race_enrolled_on_website'] = '';
            $race['race_percent_of_slots_filled'] = '';
            $race['race_enrolled_plus_group_estimates'] = '';
            $race['race_percent_with_group_estimates'] = '';
            $race['race_enrolled_target'] = '';
            $race['race_target_percent'] = '';
            // Note: This code could cause an error on the volunteer report for races that redirect, if get_races_by_company_for_company_report() is used instead of get_races_by_company()
            $race['race_on_target'] = '';
            $race['race_on_target_color_class'] = '';
        }
        else
        {
            $race['race_one_week_delta'] = $this->volunteer_shift_model->count_all_enrollments_within_last_x_days($race['race_id'], 6);
            $race['race_one_day_delta'] = $this->volunteer_shift_model->count_all_enrollments_within_last_x_days($race['race_id'], 0);

            $race['race_slots_on_website'] = $this->shift_model->count_volunteer_slots($race['race_id']);

            $race['race_enrolled_on_website'] = $this->volunteer_shift_model->count_volunteers_enrolled($race['race_id']);

            $race['race_percent_of_slots_filled'] = $race['race_slots_on_website'] != 0 ? $race['race_enrolled_on_website'] / $race['race_slots_on_website'] : 0;

            $race['race_enrolled_plus_group_estimates'] = $race['race_enrolled_on_website'] + $this->group_shift_model->count_all_promised_volunteers_for_race($race['race_id']) - $this->volunteer_shift_model->count_all_group_volunteers_enrolled_in_race($race['race_id']);

            $race['race_percent_with_group_estimates'] = $race['race_slots_on_website'] != 0 ? $race['race_enrolled_plus_group_estimates'] / $race['race_slots_on_website'] : 0;

            $race['race_enrolled_target'] = floor($race['race_slots_on_website'] * $race['race_target_percent']);

            // $race['race_target_percent'] is generated in the normal method

            if ( $race['race_status'] == 'ready' )
            {
                if ( get_pretty_percent($race['race_percent_with_group_estimates'] - $race['race_target_percent']) == '0%' )
                {
                    $race['race_on_target'] = 'Right On Target';

                    $race['race_on_target_color_class'] = 'above_target';
                }
                elseif ( $race['race_percent_with_group_estimates'] > $race['race_target_percent'] )
                {
                    $race['race_on_target'] = get_pretty_percent($race['race_percent_with_group_estimates'] - $race['race_target_percent']) . ' Above Target';

                    $race['race_on_target_color_class'] = 'above_target';
                }
                else
                {
                    $race['race_on_target'] = get_pretty_percent($race['race_target_percent'] - $race['race_percent_with_group_estimates']) . ' Below Target';

                    $race['race_on_target_color_class'] = 'below_target';
                }
            }
            else
            {
                $race['race_on_target'] = '';
                $race['race_on_target_color_class'] = '';
            }
        }
    }

    return $race;
}

models/Volunteer_shift_model.php

// 0 days = today only
// 6 days = today + 6 days
// etc.
public function count_all_enrollments_within_last_x_days($race_id, $days_to_count)
{
    assert(is_numeric($race_id));
    assert($race_id > 0);

    assert(is_integer($days_to_count));

    $date_today = date('Y-m-d', time());
    $date_earlier = date('Y-m-d', time() - $days_to_count * 60 * 60 * 24);

    $older_datetime = $date_earlier . ' 00:00:00';
    $newer_datetime = $date_today . ' 23:59:59';

    // SELECT COUNT(*) FROM volunteer_shifts WHERE race_id = 20 AND shift_deleted = 0;
    $this->db->where('race_id', $race_id);
    $this->db->where('shift_deleted', 0);
    $this->db->where('shift_added_date >', $older_datetime);
    $this->db->where('shift_added_date <', $newer_datetime);
    $this->db->from('volunteer_shifts');

    return $this->db->count_all_results();
}

public function count_volunteers_enrolled($race_id)
{
    assert(is_numeric($race_id));
    assert($race_id > 0);

    // SELECT COUNT(*) FROM volunteer_shifts WHERE race_id = 20 AND shift_deleted = 0;
    $this->db->where('race_id', $race_id);
    $this->db->where('shift_deleted', 0);
    $this->db->from('volunteer_shifts');

    return $this->db->count_all_results();
}

public function count_all_group_volunteers_enrolled_in_race($race_id)
{
    /*

    SELECT *
    FROM volunteer_shifts
    INNER JOIN volunteers ON volunteers.volunteer_id = volunteer_shifts.volunteer_id
    INNER JOIN group_shifts ON group_shifts.shift_id = volunteer_shifts.shift_id
    AND group_shifts.group_id = volunteers.group_id
    WHERE volunteer_shifts.race_id = 5
    AND volunteer_shifts.shift_deleted = 0
    AND volunteers.volunteer_deleted = 0

    */

    assert(is_numeric($race_id));
    assert($race_id > 0);

    $this->db->join('volunteers', 'volunteers.volunteer_id = volunteer_shifts.volunteer_id');
    $this->db->join('group_shifts', 'group_shifts.shift_id = volunteer_shifts.shift_id AND group_shifts.group_id = volunteers.group_id');
    $this->db->where('volunteer_shifts.race_id', $race_id);
    $this->db->where('volunteer_shifts.shift_deleted', 0);
    $this->db->where('volunteers.volunteer_deleted', 0);
    $count = $this->db->count_all_results('volunteer_shifts');

    return $count;      
}

Lines of code that generate SQL queries

$data['company'] = $this->company_model->get_company($company_id) OR load_404_page_then_die($this, $this->data);

$data = $this->race_model->get_races_by_company($company_id);

$race['list_of_shifts'] = $this->shift_model->get_race_shifts_with_extra_data($race['race_id']);

// A bunch of code in get_race_shifts_with_extra_data that runs SQL queries to generate the extra data. (Not shown here.)

$race['race_one_week_delta'] = $this->volunteer_shift_model->count_all_enrollments_within_last_x_days($race['race_id'], 6);

$race['race_one_day_delta'] = $this->volunteer_shift_model->count_all_enrollments_within_last_x_days($race['race_id'], 0);

$race['race_slots_on_website'] = $this->shift_model->count_volunteer_slots($race['race_id']);

$race['race_enrolled_on_website'] = $this->volunteer_shift_model->count_volunteers_enrolled($race['race_id']);

$this->group_shift_model->count_all_promised_volunteers_for_race($race['race_id'])

$this->volunteer_shift_model->count_all_group_volunteers_enrolled_in_race($race['race_id'])

List of SQL queries

This list is truncated because Stack Exchange posts have a maximum number of characters. The list is very long. There are 375 SQL queries total.

0.0002      SELECT GET_LOCK('55021181b8e68812ae86e331666bc3a721be4c99', 300) AS ci_session_lock 

0.0004      SELECT `data`
FROM `sessions`
WHERE `id` = '55021181b8e68812ae86e331666bc3a721be4c99' 

0.0004      SELECT *
FROM `companies`
WHERE `company_id` = 4 

0.0003      SELECT *
FROM `companies`
WHERE `company_id` = '4' 

0.0014      SELECT *
FROM `races`
WHERE `race_deleted` =0
AND `company_id` = '4'
ORDER BY `race_date` DESC 

0.0004      SELECT *
FROM `shifts`
WHERE `race_id` = '17'
AND `shift_deleted` =0
ORDER BY `shift_name` ASC, `shift_id` ASC 

0.0004      SELECT *
FROM `shifts`
WHERE `race_id` = '15'
AND `shift_deleted` =0
ORDER BY `shift_name` ASC, `shift_id` ASC 

0.0007      SELECT COUNT(*) AS `numrows`
FROM `volunteer_shifts`
WHERE `shift_id` = '55'
AND `shift_deleted` =0 

0.0002      SELECT SUM(`group_shift_spots_to_reserve`) AS `group_shift_spots_to_reserve`
FROM `group_shifts`
WHERE `shift_id` = '55'
AND `group_shift_deleted` =0 

0.0007      SELECT COUNT(*) AS `numrows`
FROM `volunteer_shifts`
JOIN `volunteers` ON `volunteers`.`volunteer_id` = `volunteer_shifts`.`volunteer_id`
JOIN `group_shifts` ON `group_shifts`.`shift_id` = `volunteer_shifts`.`shift_id` AND `group_shifts`.`group_id` = `volunteers`.`group_id`
WHERE `volunteer_shifts`.`shift_id` = '55'
AND `volunteer_shifts`.`shift_deleted` =0
AND `volunteers`.`volunteer_deleted` =0 

0.0002      SELECT `race_attrition_rate`
FROM `races`
WHERE `race_deleted` =0
AND `race_id` = '15' 

0.0006      SELECT COUNT(*) AS `numrows`
FROM `volunteer_shifts`
WHERE `shift_id` = '56'
AND `shift_deleted` =0 

0.0002      SELECT SUM(`group_shift_spots_to_reserve`) AS `group_shift_spots_to_reserve`
FROM `group_shifts`
WHERE `shift_id` = '56'
AND `group_shift_deleted` =0 

0.0006      SELECT COUNT(*) AS `numrows`
FROM `volunteer_shifts`
JOIN `volunteers` ON `volunteers`.`volunteer_id` = `volunteer_shifts`.`volunteer_id`
JOIN `group_shifts` ON `group_shifts`.`shift_id` = `volunteer_shifts`.`shift_id` AND `group_shifts`.`group_id` = `volunteers`.`group_id`
WHERE `volunteer_shifts`.`shift_id` = '56'
AND `volunteer_shifts`.`shift_deleted` =0
AND `volunteers`.`volunteer_deleted` =0 

0.0002      SELECT `race_attrition_rate`
FROM `races`
WHERE `race_deleted` =0
AND `race_id` = '15' 

0.0005      SELECT COUNT(*) AS `numrows`
FROM `volunteer_shifts`
WHERE `shift_id` = '57'
AND `shift_deleted` =0 

0.0002      SELECT SUM(`group_shift_spots_to_reserve`) AS `group_shift_spots_to_reserve`
FROM `group_shifts`
WHERE `shift_id` = '57'
AND `group_shift_deleted` =0 

0.0007      SELECT COUNT(*) AS `numrows`
FROM `volunteer_shifts`
JOIN `volunteers` ON `volunteers`.`volunteer_id` = `volunteer_shifts`.`volunteer_id`
JOIN `group_shifts` ON `group_shifts`.`shift_id` = `volunteer_shifts`.`shift_id` AND `group_shifts`.`group_id` = `volunteers`.`group_id`
WHERE `volunteer_shifts`.`shift_id` = '57'
AND `volunteer_shifts`.`shift_deleted` =0
AND `volunteers`.`volunteer_deleted` =0 

0.0002      SELECT `race_attrition_rate`
FROM `races`
WHERE `race_deleted` =0
AND `race_id` = '15' 

0.0005      SELECT COUNT(*) AS `numrows`
FROM `volunteer_shifts`
WHERE `shift_id` = '41'
AND `shift_deleted` =0 

0.0002      SELECT SUM(`group_shift_spots_to_reserve`) AS `group_shift_spots_to_reserve`
FROM `group_shifts`
WHERE `shift_id` = '41'
AND `group_shift_deleted` =0 

0.0006      SELECT COUNT(*) AS `numrows`
FROM `volunteer_shifts`
JOIN `volunteers` ON `volunteers`.`volunteer_id` = `volunteer_shifts`.`volunteer_id`
JOIN `group_shifts` ON `group_shifts`.`shift_id` = `volunteer_shifts`.`shift_id` AND `group_shifts`.`group_id` = `volunteers`.`group_id`
WHERE `volunteer_shifts`.`shift_id` = '41'
AND `volunteer_shifts`.`shift_deleted` =0
AND `volunteers`.`volunteer_deleted` =0 

0.0002      SELECT `race_attrition_rate`
FROM `races`
WHERE `race_deleted` =0
AND `race_id` = '15' 

0.0005      SELECT COUNT(*) AS `numrows`
FROM `volunteer_shifts`
WHERE `shift_id` = '42'
AND `shift_deleted` =0 

0.0002      SELECT SUM(`group_shift_spots_to_reserve`) AS `group_shift_spots_to_reserve`
FROM `group_shifts`
WHERE `shift_id` = '42'
AND `group_shift_deleted` =0 

0.0006      SELECT COUNT(*) AS `numrows`
FROM `volunteer_shifts`
JOIN `volunteers` ON `volunteers`.`volunteer_id` = `volunteer_shifts`.`volunteer_id`
JOIN `group_shifts` ON `group_shifts`.`shift_id` = `volunteer_shifts`.`shift_id` AND `group_shifts`.`group_id` = `volunteers`.`group_id`
WHERE `volunteer_shifts`.`shift_id` = '42'
AND `volunteer_shifts`.`shift_deleted` =0
AND `volunteers`.`volunteer_deleted` =0 

0.0002      SELECT `race_attrition_rate`
FROM `races`
WHERE `race_deleted` =0
AND `race_id` = '15' 

0.0005      SELECT COUNT(*) AS `numrows`
FROM `volunteer_shifts`
WHERE `shift_id` = '43'
AND `shift_deleted` =0 

0.0003      SELECT SUM(`group_shift_spots_to_reserve`) AS `group_shift_spots_to_reserve`
FROM `group_shifts`
WHERE `shift_id` = '43'
AND `group_shift_deleted` =0 

0.0007      SELECT COUNT(*) AS `numrows`
FROM `volunteer_shifts`
JOIN `volunteers` ON `volunteers`.`volunteer_id` = `volunteer_shifts`.`volunteer_id`
JOIN `group_shifts` ON `group_shifts`.`shift_id` = `volunteer_shifts`.`shift_id` AND `group_shifts`.`group_id` = `volunteers`.`group_id`
WHERE `volunteer_shifts`.`shift_id` = '43'
AND `volunteer_shifts`.`shift_deleted` =0
AND `volunteers`.`volunteer_deleted` =0 

0.0002      SELECT `race_attrition_rate`
FROM `races`
WHERE `race_deleted` =0
AND `race_id` = '15' 

0.0004      SELECT *
FROM `shifts`
WHERE `race_id` = '5'
AND `shift_deleted` =0
ORDER BY `shift_name` ASC, `shift_id` ASC 

0.0005      SELECT COUNT(*) AS `numrows`
FROM `volunteer_shifts`
WHERE `shift_id` = '9'
AND `shift_deleted` =0 

0.0002      SELECT SUM(`group_shift_spots_to_reserve`) AS `group_shift_spots_to_reserve`
FROM `group_shifts`
WHERE `shift_id` = '9'
AND `group_shift_deleted` =0 

0.0006      SELECT COUNT(*) AS `numrows`
FROM `volunteer_shifts`
JOIN `volunteers` ON `volunteers`.`volunteer_id` = `volunteer_shifts`.`volunteer_id`
JOIN `group_shifts` ON `group_shifts`.`shift_id` = `volunteer_shifts`.`shift_id` AND `group_shifts`.`group_id` = `volunteers`.`group_id`
WHERE `volunteer_shifts`.`shift_id` = '9'
AND `volunteer_shifts`.`shift_deleted` =0
AND `volunteers`.`volunteer_deleted` =0 

0.0002      SELECT `race_attrition_rate`
FROM `races`
WHERE `race_deleted` =0
AND `race_id` = '5' 

0.0005      SELECT COUNT(*) AS `numrows`
FROM `volunteer_shifts`
WHERE `shift_id` = '31'
AND `shift_deleted` =0 

0.0002      SELECT SUM(`group_shift_spots_to_reserve`) AS `group_shift_spots_to_reserve`
FROM `group_shifts`
WHERE `shift_id` = '31'
AND `group_shift_deleted` =0 

0.0006      SELECT COUNT(*) AS `numrows`
FROM `volunteer_shifts`
JOIN `volunteers` ON `volunteers`.`volunteer_id` = `volunteer_shifts`.`volunteer_id`
JOIN `group_shifts` ON `group_shifts`.`shift_id` = `volunteer_shifts`.`shift_id` AND `group_shifts`.`group_id` = `volunteers`.`group_id`
WHERE `volunteer_shifts`.`shift_id` = '31'
AND `volunteer_shifts`.`shift_deleted` =0
AND `volunteers`.`volunteer_deleted` =0 

0.0002      SELECT `race_attrition_rate`
FROM `races`
WHERE `race_deleted` =0
AND `race_id` = '5' 

0.0005      SELECT COUNT(*) AS `numrows`
FROM `volunteer_shifts`
WHERE `shift_id` = '47'
AND `shift_deleted` =0 

0.0003      SELECT SUM(`group_shift_spots_to_reserve`) AS `group_shift_spots_to_reserve`
FROM `group_shifts`
WHERE `shift_id` = '47'
AND `group_shift_deleted` =0 

0.0009      SELECT COUNT(*) AS `numrows`
FROM `volunteer_shifts`
JOIN `volunteers` ON `volunteers`.`volunteer_id` = `volunteer_shifts`.`volunteer_id`
JOIN `group_shifts` ON `group_shifts`.`shift_id` = `volunteer_shifts`.`shift_id` AND `group_shifts`.`group_id` = `volunteers`.`group_id`
WHERE `volunteer_shifts`.`shift_id` = '47'
AND `volunteer_shifts`.`shift_deleted` =0
AND `volunteers`.`volunteer_deleted` =0 

0.0002      SELECT `race_attrition_rate`
FROM `races`
WHERE `race_deleted` =0
AND `race_id` = '5' 

0.0003      SELECT *
FROM `shifts`
WHERE `race_id` = '21'
AND `shift_deleted` =0
ORDER BY `shift_name` ASC, `shift_id` ASC 

0.0006      SELECT *
FROM `shifts`
WHERE `race_id` = '23'
AND `shift_deleted` =0
ORDER BY `shift_name` ASC, `shift_id` ASC 

0.0010      SELECT COUNT(*) AS `numrows`
FROM `volunteer_shifts`
WHERE `shift_id` = '71'
AND `shift_deleted` =0 

0.0020      SELECT SUM(`group_shift_spots_to_reserve`) AS `group_shift_spots_to_reserve`
FROM `group_shifts`
WHERE `shift_id` = '71'
AND `group_shift_deleted` =0 

0.0036      SELECT COUNT(*) AS `numrows`
FROM `volunteer_shifts`
JOIN `volunteers` ON `volunteers`.`volunteer_id` = `volunteer_shifts`.`volunteer_id`
JOIN `group_shifts` ON `group_shifts`.`shift_id` = `volunteer_shifts`.`shift_id` AND `group_shifts`.`group_id` = `volunteers`.`group_id`
WHERE `volunteer_shifts`.`shift_id` = '71'
AND `volunteer_shifts`.`shift_deleted` =0
AND `volunteers`.`volunteer_deleted` =0 

0.0032      SELECT `race_attrition_rate`
FROM `races`
WHERE `race_deleted` =0
AND `race_id` = '23' 

0.0008      SELECT COUNT(*) AS `numrows`
FROM `volunteer_shifts`
WHERE `shift_id` = '72'
AND `shift_deleted` =0 

0.0003      SELECT SUM(`group_shift_spots_to_reserve`) AS `group_shift_spots_to_reserve`
FROM `group_shifts`
WHERE `shift_id` = '72'
AND `group_shift_deleted` =0 

0.0008      SELECT COUNT(*) AS `numrows`
FROM `volunteer_shifts`
JOIN `volunteers` ON `volunteers`.`volunteer_id` = `volunteer_shifts`.`volunteer_id`
JOIN `group_shifts` ON `group_shifts`.`shift_id` = `volunteer_shifts`.`shift_id` AND `group_shifts`.`group_id` = `volunteers`.`group_id`
WHERE `volunteer_shifts`.`shift_id` = '72'
AND `volunteer_shifts`.`shift_deleted` =0
AND `volunteers`.`volunteer_deleted` =0 

0.0004      SELECT `race_attrition_rate`
FROM `races`
WHERE `race_deleted` =0
AND `race_id` = '23' 

0.0008      SELECT COUNT(*) AS `numrows`
FROM `volunteer_shifts`
WHERE `shift_id` = '73'
AND `shift_deleted` =0 

0.0003      SELECT SUM(`group_shift_spots_to_reserve`) AS `group_shift_spots_to_reserve`
FROM `group_shifts`
WHERE `shift_id` = '73'
AND `group_shift_deleted` =0 

0.0008      SELECT COUNT(*) AS `numrows`
FROM `volunteer_shifts`
JOIN `volunteers` ON `volunteers`.`volunteer_id` = `volunteer_shifts`.`volunteer_id`
JOIN `group_shifts` ON `group_shifts`.`shift_id` = `volunteer_shifts`.`shift_id` AND `group_shifts`.`group_id` = `volunteers`.`group_id`
WHERE `volunteer_shifts`.`shift_id` = '73'
AND `volunteer_shifts`.`shift_deleted` =0
AND `volunteers`.`volunteer_deleted` =0 

0.0004      SELECT `race_attrition_rate`
FROM `races`
WHERE `race_deleted` =0
AND `race_id` = '23' 

0.0037      SELECT COUNT(*) AS `numrows`
FROM `volunteer_shifts`
WHERE `shift_id` = '74'
AND `shift_deleted` =0 

0.0004      SELECT SUM(`group_shift_spots_to_reserve`) AS `group_shift_spots_to_reserve`
FROM `group_shifts`
WHERE `shift_id` = '74'
AND `group_shift_deleted` =0 

0.0008      SELECT COUNT(*) AS `numrows`
FROM `volunteer_shifts`
JOIN `volunteers` ON `volunteers`.`volunteer_id` = `volunteer_shifts`.`volunteer_id`
JOIN `group_shifts` ON `group_shifts`.`shift_id` = `volunteer_shifts`.`shift_id` AND `group_shifts`.`group_id` = `volunteers`.`group_id`
WHERE `volunteer_shifts`.`shift_id` = '74'
AND `volunteer_shifts`.`shift_deleted` =0
AND `volunteers`.`volunteer_deleted` =0 

0.0003      SELECT `race_attrition_rate`
FROM `races`
WHERE `race_deleted` =0
AND `race_id` = '23' 

0.0019      SELECT COUNT(*) AS `numrows`
FROM `volunteer_shifts`
WHERE `shift_id` = '75'
AND `shift_deleted` =0 

0.0003      SELECT SUM(`group_shift_spots_to_reserve`) AS `group_shift_spots_to_reserve`
FROM `group_shifts`
WHERE `shift_id` = '75'
AND `group_shift_deleted` =0 

0.0008      SELECT COUNT(*) AS `numrows`
FROM `volunteer_shifts`
JOIN `volunteers` ON `volunteers`.`volunteer_id` = `volunteer_shifts`.`volunteer_id`
JOIN `group_shifts` ON `group_shifts`.`shift_id` = `volunteer_shifts`.`shift_id` AND `group_shifts`.`group_id` = `volunteers`.`group_id`
WHERE `volunteer_shifts`.`shift_id` = '75'
AND `volunteer_shifts`.`shift_deleted` =0
AND `volunteers`.`volunteer_deleted` =0 

0.0013      SELECT `race_attrition_rate`
FROM `races`
WHERE `race_deleted` =0
AND `race_id` = '23' 

0.0008      SELECT COUNT(*) AS `numrows`
FROM `volunteer_shifts`
WHERE `shift_id` = '76'
AND `shift_deleted` =0 

0.0003      SELECT SUM(`group_shift_spots_to_reserve`) AS `group_shift_spots_to_reserve`
FROM `group_shifts`
WHERE `shift_id` = '76'
AND `group_shift_deleted` =0 

0.0011      SELECT COUNT(*) AS `numrows`
FROM `volunteer_shifts`
JOIN `volunteers` ON `volunteers`.`volunteer_id` = `volunteer_shifts`.`volunteer_id`
JOIN `group_shifts` ON `group_shifts`.`shift_id` = `volunteer_shifts`.`shift_id` AND `group_shifts`.`group_id` = `volunteers`.`group_id`
WHERE `volunteer_shifts`.`shift_id` = '76'
AND `volunteer_shifts`.`shift_deleted` =0
AND `volunteers`.`volunteer_deleted` =0 

0.0004      SELECT `race_attrition_rate`
FROM `races`
WHERE `race_deleted` =0
AND `race_id` = '23' 

0.0010      SELECT COUNT(*) AS `numrows`
FROM `volunteer_shifts`
WHERE `shift_id` = '77'
AND `shift_deleted` =0 

0.0004      SELECT SUM(`group_shift_spots_to_reserve`) AS `group_shift_spots_to_reserve`
FROM `group_shifts`
WHERE `shift_id` = '77'
AND `group_shift_deleted` =0 

0.0008      SELECT COUNT(*) AS `numrows`
FROM `volunteer_shifts`
JOIN `volunteers` ON `volunteers`.`volunteer_id` = `volunteer_shifts`.`volunteer_id`
JOIN `group_shifts` ON `group_shifts`.`shift_id` = `volunteer_shifts`.`shift_id` AND `group_shifts`.`group_id` = `volunteers`.`group_id`
WHERE `volunteer_shifts`.`shift_id` = '77'
AND `volunteer_shifts`.`shift_deleted` =0
AND `volunteers`.`volunteer_deleted` =0 

0.0002      SELECT `race_attrition_rate`
FROM `races`
WHERE `race_deleted` =0
AND `race_id` = '23' 

0.0006      SELECT COUNT(*) AS `numrows`
FROM `volunteer_shifts`
WHERE `shift_id` = '78'
AND `shift_deleted` =0 

0.0002      SELECT SUM(`group_shift_spots_to_reserve`) AS `group_shift_spots_to_reserve`
FROM `group_shifts`
WHERE `shift_id` = '78'
AND `group_shift_deleted` =0 

0.0008      SELECT COUNT(*) AS `numrows`
FROM `volunteer_shifts`
JOIN `volunteers` ON `volunteers`.`volunteer_id` = `volunteer_shifts`.`volunteer_id`
JOIN `group_shifts` ON `group_shifts`.`shift_id` = `volunteer_shifts`.`shift_id` AND `group_shifts`.`group_id` = `volunteers`.`group_id`
WHERE `volunteer_shifts`.`shift_id` = '78'
AND `volunteer_shifts`.`shift_deleted` =0
AND `volunteers`.`volunteer_deleted` =0 

0.0002      SELECT `race_attrition_rate`
FROM `races`
WHERE `race_deleted` =0
AND `race_id` = '23' 

0.0005      SELECT COUNT(*) AS `numrows`
FROM `volunteer_shifts`
WHERE `shift_id` = '79'
AND `shift_deleted` =0 

0.0002      SELECT SUM(`group_shift_spots_to_reserve`) AS `group_shift_spots_to_reserve`
FROM `group_shifts`
WHERE `shift_id` = '79'
AND `group_shift_deleted` =0 

0.0006      SELECT COUNT(*) AS `numrows`
FROM `volunteer_shifts`
JOIN `volunteers` ON `volunteers`.`volunteer_id` = `volunteer_shifts`.`volunteer_id`
JOIN `group_shifts` ON `group_shifts`.`shift_id` = `volunteer_shifts`.`shift_id` AND `group_shifts`.`group_id` = `volunteers`.`group_id`
WHERE `volunteer_shifts`.`shift_id` = '79'
AND `volunteer_shifts`.`shift_deleted` =0
AND `volunteers`.`volunteer_deleted` =0 

0.0002      SELECT `race_attrition_rate`
FROM `races`
WHERE `race_deleted` =0
AND `race_id` = '23' 

0.0005      SELECT COUNT(*) AS `numrows`
FROM `volunteer_shifts`
WHERE `shift_id` = '80'
AND `shift_deleted` =0 

0.0004      SELECT SUM(`group_shift_spots_to_reserve`) AS `group_shift_spots_to_reserve`
FROM `group_shifts`
WHERE `shift_id` = '80'
AND `group_shift_deleted` =0 

0.0007      SELECT COUNT(*) AS `numrows`
FROM `volunteer_shifts`
JOIN `volunteers` ON `volunteers`.`volunteer_id` = `volunteer_shifts`.`volunteer_id`
JOIN `group_shifts` ON `group_shifts`.`shift_id` = `volunteer_shifts`.`shift_id` AND `group_shifts`.`group_id` = `volunteers`.`group_id`
WHERE `volunteer_shifts`.`shift_id` = '80'
AND `volunteer_shifts`.`shift_deleted` =0
AND `volunteers`.`volunteer_deleted` =0 

0.0004      SELECT `race_attrition_rate`
FROM `races`
WHERE `race_deleted` =0
AND `race_id` = '23' 

0.0005      SELECT COUNT(*) AS `numrows`
FROM `volunteer_shifts`
WHERE `shift_id` = '81'
AND `shift_deleted` =0 

0.0002      SELECT SUM(`group_shift_spots_to_reserve`) AS `group_shift_spots_to_reserve`
FROM `group_shifts`
WHERE `shift_id` = '81'
AND `group_shift_deleted` =0 

0.0006      SELECT COUNT(*) AS `numrows`
FROM `volunteer_shifts`
JOIN `volunteers` ON `volunteers`.`volunteer_id` = `volunteer_shifts`.`volunteer_id`
JOIN `group_shifts` ON `group_shifts`.`shift_id` = `volunteer_shifts`.`shift_id` AND `group_shifts`.`group_id` = `volunteers`.`group_id`
WHERE `volunteer_shifts`.`shift_id` = '81'
AND `volunteer_shifts`.`shift_deleted` =0
AND `volunteers`.`volunteer_deleted` =0 

0.0002      SELECT `race_attrition_rate`
FROM `races`
WHERE `race_deleted` =0
AND `race_id` = '23' 

0.0006      SELECT COUNT(*) AS `numrows`
FROM `volunteer_shifts`
WHERE `shift_id` = '82'
AND `shift_deleted` =0 

0.0002      SELECT SUM(`group_shift_spots_to_reserve`) AS `group_shift_spots_to_reserve`
FROM `group_shifts`
WHERE `shift_id` = '82'
AND `group_shift_deleted` =0 

0.0006      SELECT COUNT(*) AS `numrows`
FROM `volunteer_shifts`
JOIN `volunteers` ON `volunteers`.`volunteer_id` = `volunteer_shifts`.`volunteer_id`
JOIN `group_shifts` ON `group_shifts`.`shift_id` = `volunteer_shifts`.`shift_id` AND `group_shifts`.`group_id` = `volunteers`.`group_id`
WHERE `volunteer_shifts`.`shift_id` = '82'
AND `volunteer_shifts`.`shift_deleted` =0
AND `volunteers`.`volunteer_deleted` =0 

0.0002      SELECT `race_attrition_rate`
FROM `races`
WHERE `race_deleted` =0
AND `race_id` = '23' 

0.0005      SELECT COUNT(*) AS `numrows`
FROM `volunteer_shifts`
WHERE `shift_id` = '83'
AND `shift_deleted` =0 

etc.

Answer 1

Looking at your sql's you seem to be repeating the same queries in a loop. I will pick out one simple one for example, it doesn't take long to run on its own, but if you add them all up, the amount of roundtrips to the db grows.

0.0006      SELECT COUNT(*) AS `numrows`
FROM `volunteer_shifts`
WHERE `shift_id` = '82'
AND `shift_deleted` =0 

If you can get all the shift ids in advance and do something like this. This is just some pseudo code to give you an idea

SELECT shift_id FROM shifts WHERE condition....

foreach ($rows as $row) {
  $shift_ids[] = $row['shift_id'];
}

// you wind up with something like this
// $shift_ids = [75,76,77,78,79,80];

$shift_ids_str = join(", ", $shift_ids);

SELECT shift_id, COUNT(*) AS `numrows`
    FROM `volunteer_shifts`
    WHERE `shift_id` IN ($shift_ids_str)
    AND `shift_deleted` =0 

foreach ($rows as $row) {
   $shift_numrows[$row['shift_id']] = $row['numrows];
}


function lookupShiftNumRows($shift_id) {
   return isset($shift_numrows[$shift_id]) ? $shift_numrows[$shift_id] : 0;
}

Doing that alone will remove 40-50 sqls from you total, then do the same for another repeated query.

I do like using ORM's in code, but only for basic Browse Read Edit Add Delete operations.

As soon as I get to complex reports I drop back to plain sql, as the ORMs don't perform as well on large datasets, and they can often end up performing more sql's then are required.

Answer 2

Just some general advice:

• 375 queries to display 1 page does seem a lot, not only because of db execution time, but also due to transport latency and serial execution. For example: say average execution time is 1ms but communication latency is 2ms. If you do each query one after another, you have additional 375*2ms delay.

  • If you query the same table say 60 times each time to get a row or two, maybe it's better to just get whole table in a single query and process it on the server in PHP code.

  • Actually if some data changes very rarely, why don't you try caching it in memory? Only problem is when you update the data, you need to invalidate cached values. It might get tricky if you run your app on 2+ servers. There are of course well established patterns on how to handle it, it's just that it adds extra complexity, so just think about if you need it.

• If you always need to do select *, if you just need a couple of fields out of say 20, you may try putting db indexes. This would speed things up a little.

Answer 3

You might want to change/update your database structure if you need so many queries on 1 page. I would move all historical data which is not likely to change (like Company Report Data 2016) to a separate table. And then I would read aggregated data from this table.

Again, for a data that is not changing with every request I would use caching. You might want to read how to do it with PHP CodeIgniter here https://www.codeigniter.com/userguide3/database/caching.html

CodeIgniter has a coding style guide https://bcit-ci.github.io/CodeIgniter4/contributing/styleguide.html.

Class methods, property names and other variables MUST be declared in lowerCamelCase().

I would suggest to follow it.