Hi,
Let me start by saying I love datatables. they make life so much easier.
I have a mysql database which currently has 40 million rows.
Users enter search criteria on a number of columns and depending on the search I can get tens of millions of rows returned.
The time to return such a huge dataset is not insignificant.
What i'd like to know is:
Can I limit each page to say .... 500 rows, then page to the next 500.
The application is for displaying and playing encrypted calls, like when you call your bank and they say the call will be recorded for training purposes. This allows the banks etc to search, display and play them. At the moment I get 1.6 million rows a day. and growing. so as you can imagine the search times are only going to grow.
Here is my table setup.

  var mytable =  $('#calltable').DataTable( {
      processing: true,
      serverSide: true,
      ajax: "server_processing3.php",
      "jQueryUI": true,
      "pageLength": 50,
      deferRender: true,
      scrollY:     500,
      pagingType: "full_numbers",
      lengthMenu: [ [250,500,1000,2000, 5000], [250,500, 1000,2000, 5000] ],
      "ordering": true,
      "info": true,
columnDefs: [
{
    targets: 0,
    "render": function ( data, type, row, meta ) {
     switch (data){
       case 'audio/wav' :
      if(row[14] == 'Yes') {
        data = "<li class='fas fa-thumbtack' onclick='shownotes()'></li><button class='btn btn-light btn-xs'><li class='fas fa-phone-volume'  id='play'> Play </button>";
         } else {
        data = "<li class='fas fa-phone-volume'></li><button class='btn btn-light btn-xs'> Play </button>";
          }
       break;
       case 'audio/mp3' :
      if(row[14] == 'Yes') {
      data = "<li class='fas fa-thumbtack' onclick='shownotes()'></li><button class='btn btn-light btn-xs'><li class='fas fa-phone-volume'  id='play'> Play </button>";
       } else {
      data = "<button class='btn btn-light btn-xs'><li class='fas fa-phone-volume'></li> Play </button>";
      }
       break;
       case 'video/mp4' :
  if(row[14] == 'Yes') {
     data = "<li class='fas fa-thumbtack'></li><button class='btn btn-xs btn-light fas fa-video'> View </button>";
   }
  else {
    data = "<button class='btn btn-xs btn-light'><li class='fas fa-video '  id='playvideo'>  </li> View </button>";
  }
    break;
         }
     return data;
   }
},
{
        "targets": 9,
        "visible": false
    },
    {
        "targets": 11,
        "visible": true,
          "className":      'download_class',
    },
{
        "targets": 13,
        "visible": false
    },
    {
      "targets": 14,
      "visible": false

},
{
  "targets": 15,
  "visible": false

},
{
  "targets": 16,
  "visible": false
},
{
  "targets": 17,
  "visible": false
},

{
  "targets": 18,
  "visible": false
},
{
  "targets": 19,
  "visible": false
},
{
  "targets": 20,
  "visible": false
},
{
  "targets": 21,
  "visible": true
}

],
initComplete: function() {
      console.log('DT init complete in ', Date.now() - startTime + ' milliseconds.');
    }

  });

My server side processing is

<?php if (session_status() == PHP_SESSION_NONE) {
    session_start();
}
/*
 Server side processing - Used for normal table lookup
*/
include("pdo.php");
include("pdo2.php");
include("includes/functions.php");

$row_config = $pdo->query("select ccid,dbhostname from siteconfig")->fetch();
$ccid = $row_config['ccid'];
$dbhostname = $row_config['dbhostname'];
$posted = date("d-m-Y H:m");
$userkey = $_SESSION['key'];

$f = $pdo->query("select fname,lname,startTime,endTime,usefilter,filterset,userfilter,adminlevel,candownload,userid,inbound,outbound,consult,internal,audio,video,outboundhigh,outboundlow from members where activatekey ='".$userkey."'")->fetch(PDO::FETCH_ASSOC);

 $filters = $f['userfilter'];
 $outboundhigh = $f['outboundhigh']*1000;
 $outboundlow = $f['outboundlow']*1000;
 $startTime = $f['startTime'];
 $endTime = $f['endTime'];
 $userid = $f['userid'];
 $accessgroups = getgroups($userid);
 $inbound = $f['inbound'];
 $outbound = $f['outbound'];
 $consult = $f['consult'];
 $internal = $f['internal'];
 $audio = $f['audio'];
 $video = $f['video'];
 $userfilter = $f['userfilter'];
 $usefilter = $f['usefilter'];

$filters = "";

//if($usefilter == '1') { $filters .= " callId= '".$userfilter."' and "; }
if($inbound == '1')
 {
   $filters .= "   callType='Inbound'  and ";
 }
if($internal == '1'){ $filters .= "   callType='Internal'  and "; }
if($consult == '1') { $filters .= "   callType='Consult' and "; }
if($outbound == '1'){ $filters .= "   callType='Outbound' and "; }

if($audio == '1' && $video == '1')
{
  $filters .=  "  `type` = 'audio/mp3' or `type` = 'video/mp4' and ";
 } else {
if($video == '1' && $audio == '0')  { $filters .= "  `type` = 'video/mp4' and ";    }
if($audio == '1' && $video == '0')  { $filters .=  "  `type` = 'audio/mp3' and ";   }
}
if(($startTime - $endTime) > ($startTime + (86400*30)))
{
  $endTime = ($startTime=(86400*30));
}

$filters .= "start >= ".$startTime." and end <= ".$endTime." and ".$accessgroups;
$now = date("d-m-Y H:m:i");
$logfilter = addslashes($filters);
$log = $pdo2->query(" insert into error_log (posted,event) values ('$now','Filters added $logfilter')");
if(isset($_SESSION['currentFilters'])){
  $filters .= $_SESSION['currentFilters'];
  unset($_SESSION['currentFilters']);
}
  if($f['filterset'] == 'Y')
  {
  $filters = $data['userfilter'];
  }
$clearfilters = $pdo->query("update members set userfilter='', usefilter='0', filterset='N' where activatekey='".$userkey."'");
logevent(" Here are filters ".$filters);
$table = 'calls';
$primaryKey = 'id';
$columns = array(
    array( 'db' => 'type', 'dt' => 0 ),
    array( 'db' => 'agentId', 'dt' => 1 ),
    array( 'db' => 'username',     'dt' => 2),
    array( 'db' => 'duration',     'dt' => 3),
    array( 'db' => 'callerPhoneNumber',   'dt' => 4 ),
    array( 'db' => 'dialedPhoneNumber',   'dt' => 5),
    array( 'db' => 'connId',  'dt' => 6),
    array( 'db' => 'startTime',             'dt' => 7,
              'formatter' => function ($d, $row) {
              return date('d-m-Y H:i:s', strtotime($d));
                        } ),
   array( 'db' => 'stopTime',           'dt' => 8,
              'formatter' => function ($d, $row) {
              return date('d-m-Y H:i:s', strtotime($d));
                                            } ),

                        array( 'db' => 'type',         'dt' => 9),
                        array( 'db' => 'callType',     'dt' => 10),
                        array( 'db' => 'callId',       'dt' => 11),
                        array( 'db' => 'callUUID',     'dt' => 12),
                        array( 'db' => 'mediaId',      'dt' => 13),
                        array( 'db' => 'notes',        'dt' => 14),
                        array( 'db' => 'callpath',     'dt' => 15),
                        array( 'db' => 'id',           'dt' => 16),
                        array( 'db' => 'mediapath',    'dt' => 17),
                        array( 'db' => 'start',        'dt' => 18),
                        array( 'db' => 'end',           'dt' => 19),
                        array( 'db' => 'audiofile',     'dt' => 20),
                        array( 'db' => 'accessgroups',  'dt' => 21)



);
$sql_details = array(
< database config stuff >
);
 require('ssp.class.php');
echo json_encode(
    SSP::complex($_GET, $sql_details, $table, $primaryKey, $columns, null, $filters)
);

function getgroups($userid)
{
include("pdo2.php");
$myaccessgroups = "(";
$myag = $pdo2->query("select * from assignedgroups where userid='".$userid."' and assigned='Yes'");
while($ag = $myag->fetch(PDO::FETCH_ASSOC))
{
 $g = $pdo2->query("select accessgroups from accessgroups where id='".$ag['accessgroupid']."'")->fetch();
 $ag=$g['accessgroups'];
 $myaccessgroups .= "  accessgroups='".$ag."'  or";
}
 return substr($myaccessgroups,0,-3).")";
}

The code is ugly, I know, but it works... so far.

I tried playing with the ssp.class.php file paging by changing the return $limit to "LIMIT 500" but it didn't appear to make any difference.

    /**
     * Paging
     *
     * Construct the LIMIT clause for server-side processing SQL query
     *
     *  @param  array $request Data sent to server by DataTables
     *  @param  array $columns Column information array
     *  @return string SQL limit clause
     */
    public static function limit($request, $columns)
    {
        $limit = '';

        if (isset($request['start']) && $request['length'] != -1) {
            $limit = "LIMIT ".intval($request['start']).", ".intval($request['length']);
        }

        return $limit;  -> Changed this to return "LIMIT 500";
    }

I've searched forums and docs but cant seem to find the exact answer i'm after.
Any help is greatly appreciated