Hello,

I am currently using the newest DataTables with a huge mysql database (has around 5 millions of rows, 10+ columns).
I initialize my DT by

$(document).ready(function(){
    $('#detailTable').DataTable( {
        serverSide: true,
        ordering: false,
        searching: false,
        "ajax": {
            'type': 'POST',
            'url': 'getResult.php',
            'data': function(d){
                 d.cellline = getCellline();
              },
        },
        "columns": [
            { "data": "id" },
            { "data": "chrom" },
            { "data": "start" },
            { "data": "end" },
            { "data": "strand" },
            { "data": "size" },
            { "data": "method" },
            { "data": "cellline" },
            { "data": "tissue" },
            { "data": "specie" },
            { "data": "genome" }
        ],
        "processing": true,
        "language": {
            processing: '<i class="fa fa-spinner fa-spin fa-5x fa-fw" style="position:fixed; z-index: 10; top:600px; font-size: 6em; color: #39c0bc;"></i>'},
        scrollY: 300,
        scroller: {
            // loadingIndicator: false
        },
        stateSave: true
    } );
});

Whenever a user submits a form, I get the new constraint using the following function:

function getCellline(){
    return queryCellline;
}

and reload my DT by

$('#detailTable').DataTable().ajax.reload();

I load all the queried rows with the following server side script

<?php
    ini_set('memory_limit','-1');

    $cellline = $_POST['cellline'];
    $table = 'my_table';
    $primaryKey = 'id';
     
    $columns = array(
        array( 'db' => 'id',         'dt' => 'id' ),
        array( 'db' => 'chrom',      'dt' => 'chrom' ),
        array( 'db' => 'start',      'dt' => 'start' ),
        array( 'db' => 'end',        'dt' => 'end' ),
        array( 'db' => 'strand',     'dt' => 'strand' ),
        array( 'db' => 'size',       'dt' => 'size' ),
        array( 'db' => 'method',     'dt' => 'method' ),
        array( 'db' => 'cellline',   'dt' => 'cellline' ),
        array( 'db' => 'tissue',     'dt' => 'tissue' ),
        array( 'db' => 'specie',     'dt' => 'specie' ),
        array( 'db' => 'genome',     'dt' => 'genome' )
    );
     
    $sql_details = array(
        'user' => '*',
        'pass' => '*',
        'db'   => '*',
        'host' => '*'
    );
    $whereResult = null;
    $whereAll = "cellline='".$cellline."'";
    require( 'ajax/dataTables/DataTables-1.10.21/examples/server_side/scripts/ssp.class.php' );
    
    echo json_encode(
        SSP::complex( $_GET, $sql_details, $table, $primaryKey, $columns, $whereResult, $whereAll)
    );
?>

The problem is it requires about 8 minutes to load the primary data or scroll down for querying results of about 200k rows (30 seconds for 20k rows). I have played with my database and the above two queries cost only 7 and 0.87 seconds when directly running in mysql.

I'm not sure if it's possible to load the json in batches if it's too time-consuming to load the complete data. Or is there any other possible option to speed up the reloading (Not limited to the Scroller mode)?

Has anyone work with this amount of data but still had a satisfactory speed? If so, please give me some hint on how to improve the performance.

Thanks in advance!