How to optimize limit query to access data faster from a huge table?

Question

I am trying to fetch data from a table of size 9 GB + and having millions of records. I'm populating DataTable with that data. I am getting the records in chunks from the table i.e 10 per page through Ajax and SQL Limit query.

pagination

In the above image you can see We have 223,740 pages so when i try to access the last page the query takes forever to load the data. However data loads quicker when i try to access first pages. But accessing higher offset pages directly takes forever to load.

 public static function getAllEvaluationsWithNameForDataTable($start){
        $queryBuilder = new Builder();

        return  $queryBuilder
            ->from(array('e' =>  static::class))
            ->leftJoin('Cx\Framework\Models\Common\User\CxUser',  'e.cx_hc_user_id = u.id', 'u')
            ->columns('e.id, e.first_name, u.initials as assigned_coach, e.gender, e.email, e.phone, e.age, e.version, e.evaluation_status, e.ip_address, e.date_created, e.date_updated')
            ->orderBy('e.id asc')
            ->limit(10, $start)
            ->getQuery()
            ->execute()
            ->toArray();
}

PHP Function/Controller:

public function getEvaluationsAction() {
        // Enable Json response
        $this->setJsonResponse();
        // This action can be called only via ajax
        $this->requireAjax();

        // Forward to access denied if current user is not allowed to view evaluation details
        if (!$this->CxAuth->currentUserIsAllowedTo('VIEW', CxEbEvaluation::getClassResourceName()))
            return $this->forwardToAccessDeniedError();

        if(isset($_GET['start'])){
            $start = $this->request->get('start');
        }else{
            $start = 10;
        }

        $recordsTotal = count(CxEbEvaluation::getAllForDataTable(array('id')));

        //Get Evaluations from DB
        $evaluation_quizzes = CxEbEvaluation::getAllEvaluationsWithNameForDataTable(intval($start));

        //for getting base URL
        $url = new Url();

        $data = array();

        foreach ($evaluation_quizzes as $key => $quiz) {
            $data[ $key ][ 'id' ] = $quiz[ 'id' ];
            $data[ $key ][ 'first_name' ] = $quiz[ 'first_name' ];
            if($quiz[ 'assigned_coach' ]){
                $data[ $key ][ 'assigned_coach' ] = $quiz['assigned_coach'];
            }else{
                $data[ $key ][ 'assigned_coach' ] = "Not assigned";
            }

            $data[ $key ][ 'gender' ] = $quiz[ 'gender' ];
            $data[ $key ][ 'email' ] = $quiz[ 'email' ];
            $data[ $key ][ 'phone' ] = $quiz[ 'phone' ];
            $data[ $key ][ 'age' ] = $quiz[ 'age' ];
            $data[ $key ][ 'version' ] = $quiz[ 'version' ];
            $data[ $key ][ 'quiz' ] =  $url->get('/admin/get-evaluation-quiz-by-id');
            $data[ $key ][ 'manage-notes-messages-and-calls' ] =  $url->get('/admin/manage-notes-messages-and-calls');
            $data[ $key ][ 'date_created' ] = date("m/d/Y H:i:s", $quiz[ 'date_created' ]);
            $data[ $key ][ 'evaluation_status' ] = $quiz[ 'evaluation_status' ];
        }
        // Return data array
        return array(
            "recordsTotal"    => $recordsTotal,
            "recordsFiltered" => $recordsTotal ,
            "data"            => $data //How To Retrieve This Data
        );
        // Return data
    }

Javascript:

cx.common.data.cxAdminDataTables.EbEvaluation = $CxRecordsTable.cxAdminDataTable({
        ajaxUrl: '<?php echo $this->CxHelper->Route('eb-admin-get-evaluations')?>' + eqQuizIdQueryString,
        serverSide: true,
        processing: true,
        recordsFiltered :true,
        columns: [
            cx.common.admin.tableEditColumn('id',{ delete: true }),
            { data: 'first_name' },
            { data: 'assigned_coach' },
            { data: 'gender' },
            { data: 'email' },
            { data: 'phone' },
            { data: 'age' },
            cx.common.admin.tableLinkColumn('quiz', quizLinkOptions),
            cx.common.admin.tableEditColumn('id', healthCoachLinkOptions),
            cx.common.admin.tableLinkColumn('manage-notes-messages-and-calls', manageNotesMessagesAndCalls),
            { data: 'date_created' },
            cx.common.admin.tableSwitchableColumn('evaluation_status', {
                editable: true,
                createdCell: function (td, cellData, rowData, row, col){
                    $(td).data('evaluation-status-id', rowData.id);
                },
                onText: 'Complete',
                offText: 'In progress'
            })
        ],
        toolbarOptions:{
            enabled: false
        },          success: function (data) {
                            cx.common.data.cxAdminDataTables.EbEvaluation.cxAdminDataTable("reloadAjax");
                        }
                    });
                }
                else {
                    $row.removeClass('alert');
                }
            });
        }
    });

I hope the question is clear. If anything else is required just update me I will provide.

(From Comment)

SELECT  e.id` AS id, e.first_name AS first_name,
        u.initials AS assigned_coach,
        e.gender AS gender, e.email AS email, e.phone AS phone,
        e.age AS age, e.version AS version,
        e.evaluation_status AS evaluation_status,
        e.ip_address AS ip_address, e.date_created AS date_created,
        e.date_updated AS date_updated
    FROM  evaluation_client AS e
    LEFT JOIN  cx_user AS u  ON e.cx_hc_user_id = u.id
    ORDER BY  e.id ASC
    LIMIT  :APL0 OFFSET, :APL1

Answer 1

The Why does MYSQL higher LIMIT offset slow the query down? question and answers, linked by Masivuye Cokile, as well as https://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/ link provided there, contain an excellent rundown on why large offset queries are slow. Basically, for LIMIT 150000, 10 MySQL still scans the entire 150000 rows even if it discards them later. To speed it up you can either:

• use sequential pagination, i.e. "show 10 entries after ID #N", which works very fast and is a good alternative, but discards actual page number; your users will be left with "next/prev" links and/or an approximate page number you can calculate using a count query.
• or create an index on id, then force mysql to perform an index-only search.

For the second approach, you'll have to rewrite the query from

SELECT ... 
  FROM table t 
WHERE ...
ORDER by t.id ASC
LIMIT 150000, 10

to

SELECT  ...
  FROM (
        SELECT  id
        FROM    table
        ORDER BY
                id ASC
        LIMIT 150000, 10
        ) o
JOIN table t
  ON t.id = o.id
WHERE ...
ORDER BY t.id ASC

Alternatively, since you're not confined to the single query, you can retrieve the ID of the first item on your page using

SELECT id 
  FROM table 
 ORDER BY id ASC 
 LIMIT 150000, 1

then use said id to retrieve actual data:

SELECT ...
  FROM table
 WHERE id >= $id
   AND ...
 ORDER BY id ASC
 LIMIT 0, 10

Answer 2

The pattern SELECT whatever FROM vast_table ORDER BY something LIMIT 10 large_number is a notorious performance antipattern. Why? Because it has to examine a great many rows just to return a few.

If your id value is a primary key (or any indexed column) you can paginate by

SELECT whatever FROM vast_table WHERE id BETWEEN large_value AND large_value+9 ORDER BY id;

Or you might try

SELECT whatever FROM vast_table WHERE id >= large_value ORDER BY id LIMIT 10;

This doesn't paginate perfectly if your id values have gaps in them. But it performs tolerably well.

Answer 3

The issue was related to dates column datatype in my table. I was using int datatype for dates fields and when I changed the datatype of my dates column to datetime, the search results were in seconds.

Source where i found the solution @ http://dbscience.blogspot.com/2008/08/can-timestamp-be-slower-than-datetime.html