Laravel-datatables: Ajax is so slow even when removing Get() and using index for some columns

[sinaamini92]

Even though i removed get() and used index for some columns my 2.8k records takes 3secs to load (i don't know if its normal or not) and when i search through the records it takes 8-15 secs to load. I even restricted search to three columns but didn't improve at all.

//here's my contorller

public function performanceTest(Request $request)
    {
        $students = StudentProfile::join('meetings', 'student_profiles.meeting_id', '=', 'meetings.id', 'left outer')
            ->join('exam_seats', 'student_profiles.id', '=', 'exam_seats.student_id', 'left outer')
            ->join('educational_stages', 'student_profiles.educational_stage_id', '=', 'educational_stages.id', 'left outer')->select([
                'student_profiles.national_id',
                'student_profiles.first_name',
                'student_profiles.last_name',
                'educational_stages.title',
                'exam_seats.seat_no',
                'meetings.date',
                'student_profiles.created_at',
                'student_profiles.updated_at',
                'student_profiles.state_id',
                'meetings.venue_title',
            ]);

        $datatables = Datatables::of($students);
        return $datatables->make(true);
    }
// here's view :
script>

    $(function() {

        $.ajaxSetup({
            headers: {
                'X-CSRF-TOKEN': $('meta[name="csrf-token"]').attr('content')
            }
        });

        var oTable = $('#data-table-basic').DataTable({

            "initComplete": function(settings, json) {
                $('#data-table-basic_filter input').unbind();
                $('#data-table-basic_filter input').attr('placeholder','Press Enter To Search');
                $('#data-table-basic_filter input').bind('keyup', function(e) {
                    if(e.keyCode == 13) {
                        oTable.search( this.value ).draw();
                    }
                });
            },
            processing: true,
            serverSide: true,
            ajax: {
                url: '{!! route('admin-data') !!}',
                method: 'POST',
            },
            "columnDefs": [
                {
                    "targets": [ 9 ],
                    "visible": false,
                    "searchable": true
                },
                {
                    "targets": [ 2 ],
                    "visible": false
                },
                {
                    "targets": [ 6 , 5 , 7 , 8 , 9 ],
                    "searchable": false
                },
            ],
            columns: [
                { data: 'national_id', name: 'student_profiles.national_id' }, //0
                { data: 'first_name', name: 'student_profiles.first_name' }, //1
                { data: 'last_name', name: 'student_profiles.last_name' }, //2
                { data: 'title', name: 'educational_stages.title' },  //3
                { data: 'seat_no', name: 'exam_seats.seat_no' },  //4
                { data: 'date', name: 'meetings.date' },  //5
                { data: 'created_at', name: 'student_profiles.created_at' }, //6
                { data: 'updated_at', name: 'student_profiles.updated_at' }, //7
                { data: 'state_id', name: 'student_profiles.state_id' }, //8
                { data: 'venue_title', name: 'meetings.venue_title' }, //9
            ]
        });
    });
</script>

//indexing with migration :

    public function up()
    {
        Schema::table('student_profiles', function (Blueprint $table) {

            $table->index('national_id');
        });

}

• ubuntu
• PHP Version
• PHP 7.1.2 - laravel 5.4
• yajra/laravel-datatables-oracle : ^7.3

[yajra]

Have you tried running the generated sql manually? Searching is done on all columns so I guess you can make a composite index for that. Run explain plan on your sql if index scan or full table scan is being performed. Not a DBA, but surely you can do some optimization on the database level. Thanks!

[sinaamini92]

After indexing "national_id" column, all searches through the fields of the related table ("student_profiles") respond really fast, but after adding index for "seat_no" and enabling search for this field it still responds in 10 seconds. now i'm working on the solution you offered, thanks for helping and the package is awesome.