laravel datatable takes long time to load with only two records

i have the following code with only 2 records of data

my controller :

        if (request()->ajax()){
        $spareparts = Sparepart::select(DB::raw('DATE_FORMAT(spareparts.created_at,"%d-%m-%Y") as order_date'),
        DB::raw("CONCAT(users.address, ', ', regencies.name, ', ', districts.name, ', ', provinces.name) as address"),
        DB::raw("CONCAT(shipping_addresses.full_address, ', ', r.name, ', ', d.name, ', ', p.name) as shipping_address"),
        DB::raw("GROUP_CONCAT(distinct sp.nama order by sp.id SEPARATOR ', ') as nama_sparepart"),
        DB::raw("GROUP_CONCAT(distinct sp.model order by sp.id SEPARATOR ', ') as model"),
        DB::raw("GROUP_CONCAT(distinct kategori_produks.nama SEPARATOR ', ') as nama"),
        DB::raw("GROUP_CONCAT(distinct sp.quantity order by sp.id SEPARATOR ', ') as quantity"),
        DB::raw("GROUP_CONCAT(distinct sp.foto SEPARATOR ', ') as foto"),
        'spareparts.id', 'users.name', 'users.no_hp')
        ->leftJoin('sparepart_products as sp', 'spareparts.id', '=', 'sp.sparepart_id')
        ->leftJoin('users', 'spareparts.email', '=', 'users.email')
        ->leftJoin('shipping_addresses', 'users.email', '=', 'shipping_addresses.email')
        ->leftJoin('kategori_produks', 'sp.kategori_id', '=', 'kategori_produks.id')
        ->leftJoin('provinces', 'users.provinsi', '=', 'provinces.id')
        ->leftJoin('regencies', 'users.kota', '=', 'regencies.id')
        ->leftJoin('districts', 'users.kecamatan', '=', 'districts.id')
        ->leftJoin('provinces as p', 'shipping_addresses.provinsi', '=', 'p.id')
        ->leftJoin('regencies as r', 'shipping_addresses.kota', '=', 'r.id')
        ->leftJoin('districts as d', 'shipping_addresses.kecamatan', '=', 'd.id')
        ->where('spareparts.is_deleted', 0)
        ->groupBy('spareparts.id', 'shipping_addresses.full_address','r.name', 'd.name', 'p.name', 
        'regencies.name', 'districts.name', 'provinces.name')
        ->distinct();

        if($request->filled('from_date') && $request->filled('to_date')) {
            $spareparts = $spareparts->whereDate('spareparts.created_at', '>=', $request->from_date)->whereDate('spareparts.created_at', '<=', $request->to_date);
        }
        if(!empty($request->install_by)) {
            $spareparts = $spareparts->where('kategori_produks.nama', $request->install_by);
        }

        return DataTables::of($spareparts)->addColumn('action', function ($id) {
            if(Auth::user()->role == 'admin'){
                return '<a href="sparepart-request/edit/' . $id->id . '" class="btn btn-primary">Edit</a>
                        <a href="sparepart-request/' . $id->id . '/delete" class="btn btn-danger">Delete</a>';
            }
            else{
                return '<a href="sparepart-request/edit/' . $id->id . '" class="btn btn-primary">Edit</a>';
            }
        })->make();
    }

my view :

                        <table id="tbl_sparepart" class="table table-striped table-bordered nowrap" cellspacing="0"
                        width="100%">
                        <thead>
                            <tr>
                                <th>Order Date</th>
                                <th>Name</th>
                                <th>Phone Number</th>
                                <th>Address</th>
                                <th>Shipping Address</th>
                                {{-- <th>Sparepart Name</th>
                                <th>Quantity</th>
                                <th>Category</th>
                                <th>Model</th>
                                <th>Sparepart Foto</th> --}}
                                <th>Action</th>
                            </tr>
                        </thead>
                        <tbody>

                        </tbody>
                    </table>
        var table_service = $('#tbl_sparepart').DataTable({
        processing: true,
        serverSide: true,
        layout: {
            topStart: {
                'pageLength': 10,
                buttons: [{
                    extend: 'csv',
                    text: 'Export Data',
                    filename: 'sparepart'
                }]
            }
        },
        scrollX: true,
        ajax: {
            url: '/sparepart-request',
            data: function(d) {
                d.from_date = $('input[name="daterange"]').data('daterangepicker').startDate
                    .format('YYYY-MM-DD');
                d.to_date = $('input[name="daterange"]').data('daterangepicker').endDate.format(
                    'YYYY-MM-DD');
                d.category_type = $('#category_type').val();
            }
        },
        columns: [{
                data: 'order_date',
                name: 'order_date',
                searchable: false
            },
            {
                data: 'name',
                name: 'users.name'
            },
            {
                data: 'no_hp',
                name: 'users.no_hp'
            },
            {
                data: 'address',
                name: 'users.address'
            },
            {
                data: 'shipping_address',
                name: 'shipping_address',
                searchable: false
            },
            {
                data: 'nama_sparepart',
                name: 'sp.nama'
            },
            {
                data: 'quantity',
                name: 'sp.quantity'
            },
            {
                data: 'nama',
                name: 'kategori_produks.nama'
            },
            {
                data: 'model',
                name: 'sp.model'
            },
            {
                data: 'foto',
                name: 'sp.foto'
            },
            {
                data: 'action',
                name: 'action',
                orderable: false,
                searchable: false
            },
        ]
    });

and somehow it takes 10-30 seconds to load 2 records of data, but when i remove group_concat from my query, it makes the load time faster but i cant remove group_concat since i need it in my query.

i will appreciate any help, thanks in advance.

Solution

There are so many things wrong here. Anyway, here's an optimized version.

if (request()->ajax()) {
$spareparts = Sparepart::select([
        DB::raw('DATE_FORMAT(spareparts.created_at,"%d-%m-%Y") as order_date'),
        DB::raw("CONCAT(users.address, ', ', regencies.name, ', ', districts.name, ', ', provinces.name) as address"),
        DB::raw("CONCAT(shipping_addresses.full_address, ', ', r.name, ', ', d.name, ', ', p.name) as shipping_address"),
        DB::raw("GROUP_CONCAT(DISTINCT sp.nama ORDER BY sp.id SEPARATOR ', ') as nama_sparepart"),
        DB::raw("GROUP_CONCAT(DISTINCT sp.model ORDER BY sp.id SEPARATOR ', ') as model"),
        DB::raw("GROUP_CONCAT(DISTINCT kategori_produks.nama SEPARATOR ', ') as nama"),
        DB::raw("GROUP_CONCAT(DISTINCT sp.quantity ORDER BY sp.id SEPARATOR ', ') as quantity"),
        DB::raw("GROUP_CONCAT(DISTINCT sp.foto SEPARATOR ', ') as foto"),
        'spareparts.id', 'users.name', 'users.no_hp'
    ])
    ->leftJoin('sparepart_products as sp', 'spareparts.id', '=', 'sp.sparepart_id')
    ->leftJoin('users', 'spareparts.email', '=', 'users.email')
    ->leftJoin('shipping_addresses', 'users.email', '=', 'shipping_addresses.email')
    ->leftJoin('kategori_produks', 'sp.kategori_id', '=', 'kategori_produks.id')
    ->leftJoin('provinces', 'users.provinsi', '=', 'provinces.id')
    ->leftJoin('regencies', 'users.kota', '=', 'regencies.id')
    ->leftJoin('districts', 'users.kecamatan', '=', 'districts.id')
    ->leftJoin('provinces as p', 'shipping_addresses.provinsi', '=', 'p.id')
    ->leftJoin('regencies as r', 'shipping_addresses.kota', '=', 'r.id')
    ->leftJoin('districts as d', 'shipping_addresses.kecamatan', '=', 'd.id')
    ->where('spareparts.is_deleted', 0)
    ->groupBy([
        'spareparts.id', 'shipping_addresses.full_address', 'r.name', 'd.name', 'p.name',
        'regencies.name', 'districts.name', 'provinces.name'
    ]);

// Apply date range filter if provided
if ($request->filled('from_date') && $request->filled('to_date')) {
    $spareparts->whereBetween('spareparts.created_at', [
        $request->from_date, $request->to_date
    ]);
}

// Filter by install_by if provided
if ($request->filled('install_by')) {
    $spareparts->where('kategori_produks.nama', $request->install_by);
}

// Return DataTables with action column
return DataTables::of($spareparts)
    ->addColumn('action', function ($row) {
        $buttons = '<a href="sparepart-request/edit/' . $row->id . '" class="btn btn-primary">Edit</a>';
        if (Auth::user()->role == 'admin') {
            $buttons .= '<a href="sparepart-request/' . $row->id . '/delete" class="btn btn-danger">Delete</a>';
        }
        return $buttons;
    })
    ->make(true);

}

Things to consider to make it better.

Simplify Joins: Ensure that your joins are essential and properly indexed in the database. For instance, combining data from users and shipping_addresses could be revisited to see if they can be fetched separately and merged at the application level.
Avoid GROUP_CONCAT Abuse: GROUP_CONCAT may cause performance issues for large datasets. If possible, normalize this data at the database or use Laravel's hasMany relationships for nested data.
Use Select Scopes: Create query scopes for repeated patterns like filtering dates or joining specific tables.
Optimize Grouping and Aggregations: Group and aggregate only what is necessary to minimize computation.
Index Frequent Columns: Ensure columns used in WHERE, GROUP BY, and JOIN are indexed
And of course, caching