Offering patch to fix slow count queries

We found this significantly speeds up em_events_get_count. Offering it here in case it is useful to others or the main devs.

From 4c0f8e1146be31bec6a035c60aa2886695fb4c9b Mon Sep 17 00:00:00 2001
From: Samuel Reed <[email protected]>
Date: Wed, 12 Oct 2016 13:03:48 -0500
Subject: [PATCH] fix(EM): Monkey patch count() to reduce unnecessary queries
 on 5.6.6.1
---
 .../plugins/events-manager/classes/em-events.php   | 32 ++++++++++++----------
 1 file changed, 17 insertions(+), 15 deletions(-)

diff --git a/wp-content/plugins/events-manager/classes/em-events.php b/wp-content/plugins/events-manager/classes/em-events.php
index e3fedfb..82095da 100644
--- a/wp-content/plugins/events-manager/classes/em-events.php
+++ b/wp-content/plugins/events-manager/classes/em-events.php
@@ -34,22 +34,31 @@ class EM_Events extends EM_Object {
    $limit = ( $args['limit'] && is_numeric($args['limit'])) ? "LIMIT {$args['limit']}" : '';
    $offset = ( $limit != "" && is_numeric($args['offset']) ) ? "OFFSET {$args['offset']}" : '';
    $groupby_sql = '';
+   $orderby_sql = '';
 
    //Get the default conditions
    $conditions = self::build_sql_conditions($args);
    //Put it all together
    $where = ( count($conditions) > 0 ) ? " WHERE " . implode ( " AND ", $conditions ):'';
 
+   if( $count ){
+     $selectors = 'COUNT(*)';
+     $limit = '';
+     $offset = '';
+     $join = '';
+   } else {
+     $join = "LEFT JOIN $locations_table ON {$locations_table}.location_id={$events_table}.location_id";
+
      //Get ordering instructions
      $EM_Event = new EM_Event();
      $EM_Location = new EM_Location();
      $orderby = self::build_sql_orderby($args, array_keys(array_merge($EM_Event->fields, $EM_Location->fields)), get_option('dbem_events_default_order'));
+
      //Now, build orderby sql
      $orderby_sql = ( count($orderby) > 0 ) ? 'ORDER BY '. implode(', ', $orderby) : '';
 
-   //Create the SQL statement and execute
-   
-   if( !$count && $args['array'] ){
+     //Build groupby
+     if( $args['array'] ){
        $selectors_array = array();
        foreach( array_keys($EM_Event->fields) as $field_selector){
          $selectors_array[] = $events_table.'.'.$field_selector;
@@ -57,23 +66,17 @@ class EM_Events extends EM_Object {
        $selectors = implode(',', $selectors_array);
      }elseif( EM_MS_GLOBAL ){
        $selectors = $events_table.'.post_id, '.$events_table.'.blog_id';
-     $groupby_sql[] = $events_table.'.post_id, '. $events_table.'.blog_id';
+       $groupby_sql = $events_table.'.post_id, '. $events_table.'.blog_id';
      }else{
        $selectors = $events_table.'.post_id';
-     $groupby_sql[] = $events_table.'.post_id'; //prevent duplicates showing in lists
+       $groupby_sql = $events_table.'.post_id'; //prevent duplicates showing in lists
      }
-   if( $count ){
-     $selectors = 'SQL_CALC_FOUND_ROWS *';
-     $limit = 'LIMIT 1';
-     $offset = 'OFFSET 0';
    }
 
-   //add group_by if needed
-   $groupby_sql = !empty($groupby_sql) && is_array($groupby_sql) ? 'GROUP BY '.implode(',', $groupby_sql):''; 
-   
+   //Create the SQL statement and execute
    $sql = apply_filters('em_events_get_sql',"
      SELECT $selectors FROM $events_table
-     LEFT JOIN $locations_table ON {$locations_table}.location_id={$events_table}.location_id
+     $join
      $where
      $groupby_sql $orderby_sql
      $limit $offset
@@ -81,8 +84,7 @@ class EM_Events extends EM_Object {
 
    //If we're only counting results, return the number of results
    if( $count ){
-     $wpdb->query($sql);
-     return apply_filters('em_events_get_count', $wpdb->get_var('SELECT FOUND_ROWS()'), $args);    
+     return apply_filters('em_events_get_count', $wpdb->get_var($sql), $args);
    }
    $results = $wpdb->get_results( $sql, ARRAY_A);
-- 
2.10.0