Concat tidak berfungsi untuk indexing

 $sql = "SELECT a.* , CONCAT(a.Kode_Barang,'<br><b>',b.Nm_Aset5,'</b>') as Nm_Aset5, CONCAT(a.latitude,'<br>',a.longitude) as latitude, c.Uraian as Kondisi from ta_kib_c as a left join ref_rek5_108 as b on CONCAT(a.Kd_Aset8,a.Kd_Aset80,a.Kd_Aset81,a.Kd_Aset82,a.Kd_Aset83,a.Kd_Aset84,a.Kd_Aset85)=CONCAT(b.Kd_Aset,b.Kd_Aset0,b.Kd_Aset1,b.Kd_Aset2,b.Kd_Aset3,b.Kd_Aset4,b.Kd_Aset5) LEFT JOIN ref_kondisi as c on a.Kondisi=c.Kd_Kondisi";  

Dari query Anda, ada beberapa hal yang bikin lambat saat data ratusan ribu baris:

1. Pakai SELECT a.* → ini ambil semua kolom, padahal tidak semuanya dipakai.

2. Pakai CONCAT(...) di JOIN dan WHERE → ini bikin index di tabel tidak bisa dipakai (selalu full scan).

3. Search LIKE "%keyword%" ke semua kolom → ini berat sekali kalau tabel besar, apalagi tanpa index.

4. Hitung total pakai query lengkap → harusnya cukup COUNT(*) dengan kondisi filter.

---

✨ Perbaikan Step by Step

1. Pilih hanya kolom yang dipakai

Misalnya:

SELECT 
   a.ID, a.Kode_Barang, a.Tgl_Perolehan, a.Harga, 
   CONCAT(a.Kode_Barang,'<br><b>',b.Nm_Aset5,'</b>') AS Nm_Aset5,
   CONCAT(a.latitude,'<br>',a.longitude) AS latitude,
   c.Uraian AS Kondisi

➡️ jangan SELECT a.*.

---

2. Hindari CONCAT() di JOIN

Anda punya ini:

ON CONCAT(a.Kd_Aset8,a.Kd_Aset80,a.Kd_Aset81,a.Kd_Aset82,a.Kd_Aset83,a.Kd_Aset84,a.Kd_Aset85)
= CONCAT(b.Kd_Aset,b.Kd_Aset0,b.Kd_Aset1,b.Kd_Aset2,b.Kd_Aset3,b.Kd_Aset4,b.Kd_Aset5)

⚡ Ini bikin MySQL tidak bisa pakai index.
Lebih cepat kalau dibandingkan per kolom:

ON  a.Kd_Aset8  = b.Kd_Aset
AND a.Kd_Aset80 = b.Kd_Aset0
AND a.Kd_Aset81 = b.Kd_Aset1
AND a.Kd_Aset82 = b.Kd_Aset2
AND a.Kd_Aset83 = b.Kd_Aset3
AND a.Kd_Aset84 = b.Kd_Aset4
AND a.Kd_Aset85 = b.Kd_Aset5

---

3. Hindari CONCAT di filter OPD

Anda tulis:

WHERE CONCAT(a.Kd_Bidang,a.Kd_Unit) = '$a1'

Lebih cepat kalau pakai AND:

WHERE a.Kd_Bidang = '$kd_bidang'
  AND a.Kd_Unit   = '$kd_unit'

Kalau $a1 isinya gabungan, sebaiknya pecah dulu di PHP (substr atau explode) baru masukkan ke query.

---

4. Optimalkan search

Sekarang search Anda looping semua kolom:

for ($i=0; $i<count($columns); $i++) {
   $where_sql .= $columns[$i].' LIKE "%'.$search.'%"';
}

➡️ ini berat kalau kolom banyak.

Solusi:

• Batasi ke beberapa kolom penting (misalnya kode barang, nama aset, kondisi).

• Gunakan FULLTEXT INDEX kalau banyak teks.

Contoh:

WHERE (a.Kode_Barang LIKE '%$search%' OR b.Nm_Aset5 LIKE '%$search%')

---

5. Hitung total dengan COUNT(*)

Sekarang Anda pakai:

$count = $database->query($sql);
$totaldata = $count->num_rows;

➡️ ini jalankan query penuh (dengan join dan limit hilang).

Lebih baik:

$sqlCount = "SELECT COUNT(*) 
             FROM ta_kib_c a
             LEFT JOIN ref_rek5_108 b ON ...
             LEFT JOIN ref_kondisi c ON ...
             $whereClause";

$totaldata = $database->query($sqlCount)->fetch_row()[0];

---

🚀 Contoh Query yang Lebih Cepat

$sql = "SELECT 
           a.ID, a.Kode_Barang, a.Tgl_Perolehan, a.Harga,
           CONCAT(a.Kode_Barang,'<br><b>',b.Nm_Aset5,'</b>') AS Nm_Aset5,
           CONCAT(a.latitude,'<br>',a.longitude) AS latitude,
           c.Uraian AS Kondisi
        FROM ta_kib_c AS a
        LEFT JOIN ref_rek5_108 AS b 
             ON  a.Kd_Aset8  = b.Kd_Aset
             AND a.Kd_Aset80 = b.Kd_Aset0
             AND a.Kd_Aset81 = b.Kd_Aset1
             AND a.Kd_Aset82 = b.Kd_Aset2
             AND a.Kd_Aset83 = b.Kd_Aset3
             AND a.Kd_Aset84 = b.Kd_Aset4
             AND a.Kd_Aset85 = b.Kd_Aset5
        LEFT JOIN ref_kondisi AS c 
             ON a.Kondisi = c.Kd_Kondisi
        WHERE 1=1";

// filter dinamis
if ($kd_KA != '') {
    $sql .= " AND a.Kd_KA = '$kd_KA'";
}
if ($parameter == '1') {
    $sql .= " AND YEAR(a.Tgl_Perolehan) <= '$thn'";
} elseif ($parameter == '2') {
    $sql .= " AND YEAR(a.Tgl_Perolehan) = '$thn'";
}

// filter OPD
if ($a1 != '0' && $a2 == '0') {
    $sql .= " AND a.Kd_Bidang = '".substr($a1,0,2)."' 
              AND a.Kd_Unit   = '".substr($a1,2,2)."'";
} elseif ($a2 != '0' && $a3 == '0') {
    $sql .= " AND a.Kd_Bidang = '".substr($a2,0,2)."' 
              AND a.Kd_Unit   = '".substr($a2,2,2)."' 
              AND a.Kd_Sub    = '".substr($a2,4,2)."'";
} elseif ($a3 != '0') {
    $sql .= " AND a.Kd_Bidang = '".substr($a3,0,2)."' 
              AND a.Kd_Unit   = '".substr($a3,2,2)."' 
              AND a.Kd_Sub    = '".substr($a3,4,2)."' 
              AND a.Kd_UPB    = '".substr($a3,6,2)."'";
}

// search
if (!empty($_GET['search']['value'])) {
    $search = $_GET['search']['value'];
    $sql .= " AND (a.Kode_Barang LIKE '%$search%' 
              OR b.Nm_Aset5 LIKE '%$search%' 
              OR c.Uraian LIKE '%$search%')";
}

// order
$sortColumn = $columns[$_GET['order'][0]['column']];
$sortDir = $_GET['order'][0]['dir'];
$sql .= " ORDER BY $sortColumn $sortDir 
          LIMIT $start, $length";

---

⚡ Dengan ini:

• Index bisa dipakai (JOIN & WHERE tidak pakai CONCAT).

• Query count lebih ringan (COUNT(*)).

• Search lebih efisien (hanya kolom penting).

---

Mau saya bikinkan versi full siap pakai (PHP lengkap dengan JSON response untuk DataTables) biar tinggal copy-paste ke load-data.php Anda?