Hello all,

This is a query I've been thinking about for quite some time that I would like to optimize to run efficiently in MySQL. It began life in an ASP/Access app about 12 years ago that ran a straight (no joins) query against an Access database of contacts returning about 600 rows. That was saved in an array which was then looped and each row ran another query against a couple of tables that pulled out yearly grade information. It's not the most efficient way of doing it but it worked.

Now we're using a more sophisticated CRM on PHP/MySQL called CiviCRM and I'm rebuilding this part of the system as a custom plugin.

In order to achieve the same results as the old system, I've duplicated what we had originally done to get a baseline to start with. It's dead slow compared to the old ASP/Access version so I have some work to do.

Here's how I'm approaching it:

I have a main query to pull in the contact information as follows:

PHP Code:

$sql 
"SELECT contact.id
        , contact.last_name
        , contact.first_name
        , contact.external_identifier
        , contact.user_unique_id
        , membership.membership_type_id
        , membership.id AS membership_id
        , membership.join_date AS member_since
        , membership_type.name AS member_type

        FROM contact 
        
        INNER JOIN membership
                ON contact.id = membership.contact_id
        
        INNER JOIN membership_type
                ON membership.membership_type_id = membership_type.id
        
        WHERE contact.first_name IS NOT NULL 
        
        AND contact.last_name IS NOT NULL
        
        ORDER BY contact.last_name"

The CRM has a database abstraction layer that allows me to get a data access object and I can either loop in into an array or loop and execute a query for each row in my main query. I've found it is slightly more efficient looping directly from the dataset so here's how that part works (I've skipped a bunch of the html cruft involved to keep the concept from getting muddled up):

Anything in the code below that is prefixed with $dao-> is a value from the results of the query above and specific to the row that is in focus during that iteration of the While loop.

PHP Code:

// Execute the query above and return a dataset object 
$dao CRM_Core_DAO::executeQuery($sql);

// START HTML string variable to hold a table of results $report_table = "<table>, etc..";


while( $dao->fetch( ) ) {   

        
$sql "SELECT professional_development_categories.id AS id
                 , professional_development_categories.category AS category
                 , SUM(professional_development_activities.credits) AS credits
                 , professional_development_categories.minimum
                 , professional_development_categories.maximum
                 , professional_development_categories.description
                 FROM professional_development_categories
                 LEFT OUTER JOIN professional_development_activities
                 ON professional_development_activities.category_id = professional_development_categories.id
                 AND professional_development_activities.contact_id = " 
$dao->id "
                 AND EXTRACT(YEAR FROM professional_development_activities.credit_date) = " 
$_SESSION["report_year"] . "
                 GROUP BY professional_development_categories.id"
;

        
// Populate the variable that holds the HTML table of results
        
$report_table .= '<tr>';
        
$report_table .= '<td>' $dao->last_name '</td>';
        
$report_table .= '<td>' $dao->first_name '</td>';
        
$report_table .= '<td>' $dao->member_type '</td>';
        
$report_table .= '<td>' $dao->member_since '</td>';

        
// Execute the sub query for each row in the main query to get this years scores for that individual
        
$subdao CRM_Core_DAO::executeQuery($sql);
            
        
$total_credits 0;
        
$sub_cells "";
                
        while( 
$subdao->fetch( ) ) { 
                 
$total_credits += abs($subdao->credits);             
                 
$sub_cells .= '<td>' abs($subdao->credits) . '</td>';
        }
            
        
$report_table .= '<td>' $total_credits '</td>';
        
$report_table .= $sub_cells;

        
$report_table .= '</tr>';

}


// END HTML to create a table of results $report_table .= "</table>, etc.."; 
So hopefully that isn't too convoluted and makes some sense. I'm looking to make some improvements to it and speed it up. I'd like to combine the queries but I'm not sure how I can do that.

Thanks,
Andrew