Tolong pilih kategori sesuai, jenis posting (diskusi atau bukan) dan sertakan tag/topik yang sesuai seperti komputer, java, php, mysql, dll. Promosi atau posting tidak pada tempatnya akan kami hapus!
- Bagi Anda yang ingin mendaftar, baca link berikut:
http://diskusiweb.com/discussion/50491/how-to-registrasi-diskusiweb-com-baca-ini-terlebih-dahulu
- Cara menyisipkan kode program supaya tampil rapi dan terformat dengan baik di diskusiweb.com: http://www.diskusiweb.com/discussion/50415/cara-menyisipkan-kode-program-di-diskusiweb-com
- Cara posting gambar/image di post Anda: http://www.diskusiweb.com/discussion/47345/cara-menyisipkan-menyertakan-image-pada-posting/p1

Mysql Query Performance

Halo dwebber, mo nanya lagi nih untuk query select di mysql.

Langsung aja gw punya table seperti ini:
CREATE TABLE `invoice` (
`invoice_id` BIGINT(20) NOT NULL AUTO_INCREMENT,
`invoice_product_member_id` INT(10) UNSIGNED NOT NULL,
`invoice_member_id` INT(10) UNSIGNED NOT NULL,
`invoice_product_id` INT(10) UNSIGNED NOT NULL,
`invoice_periode` INT(10) UNSIGNED NOT NULL,
`invoice_value` FLOAT(10,2) UNSIGNED NOT NULL DEFAULT '0.00',
`invoice_paid` DOUBLE(10,2) UNSIGNED NOT NULL DEFAULT '0.00',
`invoice_date` DATE NULL DEFAULT NULL,
`invoice_due_date` DATE NULL DEFAULT NULL,
`invoice_input_datetime` DATETIME NULL DEFAULT NULL,
`invoice_paid_datetime` DATETIME NULL DEFAULT NULL,
`invoice_note` VARCHAR(512) NULL DEFAULT NULL,
`invoice_status` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '0 = normal 1= dibayarkan oleh kas umum',
PRIMARY KEY (`invoice_id`),
INDEX `invoice_product_member_id` (`invoice_product_member_id`),
INDEX `invoice_member_id` (`invoice_member_id`),
INDEX `invoice_product_id` (`invoice_product_id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB



dengan query seperti ini
function get_data_invoice($start, $end, $where_filter, $sort, $dir, $admin_product_type) {
$sql_sort = '';
if ($sort != '') {
$sql_sort = " ORDER BY " . $sort . " " . $dir;
}
$type = '';
if ($admin_product_type != '') {
$type = "AND product_type = '" . $admin_product_type . "'";
}
 
$sql = "
SELECT * FROM (
SELECT
invoice.*,
product_member_code, product_code, product_name, product_type,
member_code, member_name
FROM invoice
JOIN product_member ON product_member_id = invoice_product_member_id
JOIN product ON product_id = invoice_product_id
JOIN member ON member_id = invoice_member_id
) result
WHERE $where_filter $type $sql_sort LIMIT $start, $end
";
$query = $this->db->query($sql);
 
return $query;
}
 
function count_data_invoice($where_filter, $admin_product_type) {
 
$type = '';
if ($admin_product_type != '') {
$type = "AND product_type = '" . $admin_product_type . "'";
}
 
$sql = "
SELECT COUNT(*) as total FROM (
SELECT
invoice.*,
product_member_code, product_code, product_name, product_type,
member_code, member_name
FROM invoice
JOIN product_member ON product_member_id = invoice_product_member_id
JOIN product ON product_id = invoice_product_id
JOIN member ON member_id = invoice_member_id
) result WHERE $where_filter $type
";
$query = $this->db->query($sql);
$row = $query->row();
 
return $row->total;
}


dengan data sekitar 300rb, ini butuh waktu sekitar 20 detik, padahal sudah di paging.

ada ide untuk mempercepat query ini?

thanks ;)
Tagged:

Comments

  • edited May 2014
    Keterangan tambahan:
    data invoice: 300rb. 
    data member: 7500. 
    data product member: 10rb. data product: 175.
  • edited September 2014
    [1]
    innodb nya single file atau per table per file ?

    [2]
    yg ini :

    SELECT * FROM (
      SELECT
      invoice.*,
      product_member_code, product_code, product_name, product_type,
      member_code, member_name
      FROM invoice
      JOIN product_member ON product_member_id = invoice_product_member_id
      JOIN product ON product_id = invoice_product_id
      JOIN member ON member_id = invoice_member_id
    ) result
    WHERE $where_filter $type $sql_sort LIMIT $start, $end

    seandainya ditulis kaya begini :

    SELECT
    invoice.*,
    product_member_code, product_code, product_name, product_type,
    member_code, member_name
    FROM invoice
    JOIN product_member ON product_member_id = invoice_product_member_id
    JOIN product ON product_id = invoice_product_id
    JOIN member ON member_id = invoice_member_id
    WHERE $where_filter $type $sql_sort LIMIT $start, $end

    ada masalah apa ?

    kalo masalah di nama table rujukan :

    SELECT
    invoice.*,
    product_member_code, product_code, product_name, product_type,
    member_code, member_name
    FROM invoice
    JOIN product_member ON product_member_id = invoice_product_member_id
    JOIN product ON product_id = invoice_product_id
    JOIN member ON member_id = invoice_member_id
    HAVING $where_filter $type $sql_sort LIMIT $start, $end

    [3]
    kalo masalah jumlah total record buat paging ...
    coba pake : SQL_CALC_FOUND_ROWS
    http://www.diskusiweb.com/discussion/43123/paging-php-mysql/p1
  • [1]
    innodb nya single file atau per table per file ?
    => yang dimaksud file apa ya? tiap2 table innodb.

    [2]
    tidak masalah, karena tidak ada column alias, jadi saat di filter tetap pake nama field asli.
    sebenarnya dibuat seperti itu jika ada column yang pake alias aja, biasanya hasil arithmetic.

    [3]
    ok gw cek dulu.


    tambahan, aneh nya table transaksi yang data nya lebih dari itu,
    bisa jalan dengan sangat cepat, di explain query nya, dah 1 row tiap key nya.

    kalo yang invoice itu kalo di explain query, mesti field invoice_product_id dapet nya banyak bgt,
    invoice_member_id juga hasil nya banyak banget, jadi mysql kayak ignore index nya.
  • [1]
    daaannnnnn ... :))

    innodb itu default nya seluruh table yg bertipe innodb bakal disimpan jadi 1 file : ibdata*
    gede nya bisa ampun-ampunan, gw pernah dapat 1 file 400GB

    ya, dikira sajalah kalo OS mesti handle file yg oversize
    pasti lebih berat dibanding kalo file nya kecil

    kalo seting innodb diubah jadi per table per file
    ya tiap table innodb bakal punya file sendiri-sendiri yg terpisah
    persis myisam

    http://www.diskusiweb.com/discussion/comment/263061#Comment_263061

    [2]
    yo wis, hindari sub query kalo kagak perlu
  • lanjut :D

    gw dah pake SQL_CALC_FOUND_ROWS
    berhasil mangkas waktu dari 15,19 detik jadi 7,35 detik. Lumayan hehehe kamsiah :p
  • oh berarti per table per file innodb nya hehehe
  • di local dan turun drastis waktu nya, di cobain di pc server lebih cepet lagi :D
  • wis ? beres ?
  • dah beres pak, dah bisa di tekan sampai 3 detik, thank u ;)
  • yo wis sekarang nungging :))

    * mau gw pancal * =))
  • kalo pake
    SELECT * FROM (
                    SELECT SQL_CALC_FOUND_ROWS
                    product_member_id
                ) result

    ini gak bisa ya?

    error nya:
    Incorrect usage/placement of 'SQL_CALC_FOUND_ROWS'
  • itu mau dipake di dalam sub query ?
    ya kagak bisa kalo di dalam sub query
  • edited May 2014
    hmm gak bisa ya, yo wis, gantian hayo nungging

    =))
  • mau elo apain ? ;))






    =))
Sign In or Register to comment.