Removing SQL_CALC_FOUND_ROWS from query in ssp class

dowzer

I am using SSP for a large dataset and performance is VERY poor - after a lot of optimising and indexing I have discovered the issue is with SQL_CALC_FOUND_ROWS.

If I run the query with SQL_CALC_FOUND_ROWS it takes 36 seconds directly in mySQL but without it takes around 0.4 seconds!

I can remove SQL_CALC_FOUND_ROWS dro the queries below but it breaks pagination so is there another way around this?

Thanks
Jason

` /**
* Perform the SQL queries needed for an server-side processing requested,
* utilising the helper functions of this class, limit(), order() and
* filter() among others. The returned array is ready to be encoded as JSON
* in response to an SSP request, or can be modified if needed before
* sending back to the client.
*
* @param array $request Data sent to server by DataTables
* @param array $sql_details SQL connection details - see sql_connect()
* @param string $table SQL table to query
* @param string $primaryKey Primary key of the table
* @param array $columns Column information array
* @param array $joinQuery Join query String
* @param string $extraWhere Where query String
*
* @return array Server-side processing response array
*
*/
static function simple ( $request, $sql_details, $table, $primaryKey, $columns, $qas, $joinQuery = NULL, $extraWhere = '', $groupBy = '', $_having = [])
{
$bindings = array();
$db = SSP::sql_connect( $sql_details );

    // Build the SQL query string from the request
    $limit = SSP::limit( $request, $columns );
    $order = SSP::order( $request, $columns, $joinQuery );
    $where = SSP::filter( $request, $columns, $bindings, $joinQuery );
    $wheretot = '';

    SSP::debug($where, 'where');
    // IF Extra where set then set and prepare query
    if($extraWhere) {
        if($qas){
            $wheretot = " AS `$qas` WHERE $extraWhere";
        }else{
            $wheretot = " WHERE $extraWhere";
        }

        if ($_having) {
            $having = ' HAVING '. $_having["query"] .' ';
            $bindings = $_having["bindings"];
            $extraWhere = ' WHERE '.$extraWhere;
            $where = '';
        } else {
            $having = '';
            $extraWhere = ($where) ? ' AND '.$extraWhere : ' WHERE '.$extraWhere;
        }

    }

    $groupBy = ($groupBy) ? ' GROUP BY '.$groupBy .' ' : '';

    // Main query to actually get the data
    if($joinQuery){
        $col = SSP::pluck($columns, 'db', $joinQuery);
        $query =  "SELECT SQL_CALC_FOUND_ROWS ".implode(", ", $col)."
         $joinQuery
         $where
         $extraWhere
         $groupBy
         $having
         $order
         $limit";
         SSP::debug($query,'query');
    }else{
        $query =  "SELECT SQL_CALC_FOUND_ROWS `".implode("`, `", SSP::pluck($columns, 'db'))."`
         FROM `$table`
         $where
         $extraWhere
         $groupBy
         $having
         $order
         $limit";
    }

    $data = SSP::sql_exec( $db, $bindings,$query);

    // Data set length after filtering
    $resFilterLength = SSP::sql_exec( $db,
        "SELECT FOUND_ROWS()"
    );
    $recordsFiltered = $resFilterLength[0][0];

    // Total data set length
    $resTotalLength = SSP::sql_exec( $db,
        "SELECT COUNT({$primaryKey})
         FROM   $table $wheretot"
    );
    $recordsTotal = $resTotalLength[0][0];

    /*
     * Output
     */
    return array(
        "draw"            => intval( $request['draw'] ),
        "recordsTotal"    => intval( $recordsTotal ),
        "recordsFiltered" => intval( $recordsFiltered ),
        "data"            => SSP::data_output( $columns, $data, $joinQuery )
    );
}`

allan

With server-side processing you need three pieces of information / queries:

1. The number of records in the full table - normally a count(id) is fast for that in MySQL.
2. The number of records in the filtered table - that is what we use the found rows for, but you could apply another query that simply does a count with the where condition.
3. The data to show for the current page.

The first is used to show the number of rows in the unfiltered table. The second is used for the paging calculations and the third obviously to show the current page.

So the answer is that you do need to know how many records there are under the current filtering constraints (assuming you have some). The found rows should be the fastest way of doing that, but it would be interesting to try and replace that in your specific case with a simple select count(*) from ... where ....

Allan