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?

Menambahkan field diluar field tabel pada datatable

if (isset($_GET['search']['value']) && $_GET['search']['value'] != '') {

    $search = $_GET['search']['value'];

    $where_sql .= ' (';

    // create parameter pencarian kesemua kolom yang tertulis

    // di $columns

    for ($i = 0; $i < count($columns); $i++) {

        if ($columns[$i] != 'Kapitalisasi') {

            $where_sql .= $columns[$i] . ' LIKE "%' . $search . '%" ';

        }

        // agar tidak menambahkan 'OR' diakhir Looping

        if ($i < count($columns) - 2) {

            $where_sql .= ' OR ';

        }

        // echo $where_sql;

    }

    $where_sql  .= ' )';

}

// die();

// echo $where_sql;

if (count($where) > 0) {

    $where = join(" and ", $where);

    $where = "where $where ";

} else {

    $where = '';

}

if ($where_sql != '') {

    $sql .= " $where AND $where_sql";

} else {

    $sql .= " $where";

} 

Membuat query untuk menampilkan data yang tidak ada pada tabel relasi

 SELECT a.*,

       b.Nm_Aset5 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

LEFT JOIN ta_kibcr AS d 

       ON d.IDPemda = a.IDPemda

WHERE  d.IDPemda IS NULL

menambahkan CSS untuk type file

 <style>

/* Bungkus input file */

.custom-file {

    position: relative;

    display: inline-block;

    width: 100%;

    max-width: 400px;

}

/* Hilangkan tampilan default */

.custom-file input[type="file"] {

    position: absolute;

    left: 0;

    top: 0;

    opacity: 0;

    width: 100%;

    height: 100%;

    cursor: pointer;

}

/* Tombol custom */

.custom-file-label {

    display: flex;

    align-items: center;

    justify-content: space-between;

    background: #f8f9fa;

    border: 1px solid #ced4da;

    border-radius: 8px;

    padding: 10px 15px;

    font-size: 14px;

    color: #495057;

    cursor: pointer;

    transition: all 0.2s ease-in-out;

}

/* Hover effect */

.custom-file-label:hover {

    background: #e9ecef;

    border-color: #adb5bd;

}

/* Nama file tampil */

.file-name {

    font-style: italic;

    color: #6c757d;

    margin-left: 10px;

    flex: 1;

    white-space: nowrap;

    overflow: hidden;

    text-overflow: ellipsis;

}

</style>

<div class="custom-file">

    <label for="uploadFile" class="custom-file-label">

        Pilih File <span class="file-name">(*Max 2 MB)</span>

    </label>

    <input type="file" id="uploadFile" name="uploadFile" multiple>

</div>

<script>

document.getElementById("uploadFile").addEventListener("change", function() {

    const files = this.files;

    const maxSize = 2 * 1024 * 1024; // 2 MB

    let fileNames = [];

    for (let i = 0; i < files.length; i++) {

        if (files[i].size > maxSize) {

            alert("File " + files[i].name + " melebihi batas 2 MB!");

            this.value = "";

            document.querySelector(".file-name").textContent = "(*Max 2 MB)";

            return;

        }

        fileNames.push(files[i].name);

    }

    // tampilkan nama file

    document.querySelector(".file-name").textContent = fileNames.join(", ");

});

</script>

Merubah Collation pada database

🔹 Cara aman kalau ada FOREIGN KEY Matikan constraint sementara 

 SET FOREIGN_KEY_CHECKS=0; lalu jalankan semua 

ALTER TABLE (nama_tabel)  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; 

 Nyalakan lagi constraint SET FOREIGN_KEY_CHECKS=1;