优化查询，php mysql

I have 9k-20k records and using indexing. and the result is 2 minutes for input and 5 minutes for query report.

When I use php, it still need loop every product, cz little complicated report I need to create so if it's more nice if the query time is increased. Thanks for help.

Mysql query :

EXPLAIN select barang.merk, sum(new.total)
from barang,
(
    SELECT 
        master.kode_barang,sum(master.jumlah_satuan_Terkecil) as total
    FROM 
        master
    Group by 
        master.kode_barang
) as new
where new.kode_barang=barang.nama_barang
group by barang.merk order by barang.id

result :

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra 
1   PRIMARY     <derived2>  ALL     NULL    NULL    NULL    NULL    85  Using temporary; Using filesort
1   PRIMARY     barang  index   NULL    id  230     NULL    99  Using where; Using index; Using join buffer
2   DERIVED     master  index   NULL    id  175     NULL    9158    Using index; Using temporary; Using filesort

My structure :

CREATE TABLE IF NOT EXISTS `master` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tanggal_nota` varchar(25) NOT NULL,
  `no_nota` varchar(15) NOT NULL,
  `nama_sales` varchar(60) NOT NULL,
  `kode_kelurahan` varchar(25) NOT NULL,
  `nama_customer_master` varchar(60) NOT NULL,
  `alamat` varchar(100) NOT NULL,
  `kode_barang` varchar(25) NOT NULL,
  `jumlah_satuan_terkecil` varchar(15) NOT NULL,
  `dpp_ppn` varchar(15) NOT NULL,
  `persen_diskon_hco` int(15) NOT NULL,
  `harga_hd` int(15) NOT NULL,
  `value_hd` int(15) NOT NULL,
  `kode_subkategori` varchar(5) NOT NULL,
  `id_file` varchar(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id` (`id`,`tanggal_nota`,`no_nota`,`nama_sales`,`kode_barang`,`jumlah_satuan_terkecil`,`kode_subkategori`,`id_file`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Another tabel is looks like this :

CREATE TABLE IF NOT EXISTS `barang` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nama_barang` varchar(100) NOT NULL,
  `merk` varchar(60) NOT NULL,
  `kategori` varchar(60) NOT NULL,
  `principal` varchar(60) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `id` (`id`,`nama_barang`,`merk`,`kategori`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

My index

Action  Keyname Type    Unique  Packed  Column  Cardinality Collation   Null    Comment
Edit Edit   Drop Drop   PRIMARY BTREE   Yes No  id  9254    A       
Edit Edit   Drop Drop   id  BTREE   No  No  id  9254    A       
tanggal_nota    9254    A   
no_nota 9254    A   
nama_sales  9254    A   
kode_barang 9254    A   
jumlah_satuan_terkecil  9254    A   
kode_subkategori    9254    A   
id_file 9254    A   YES

another index :

Action  Keyname Type    Unique  Packed  Column  Cardinality Collation   Null    Comment
Edit Edit   Drop Drop   PRIMARY BTREE   Yes No  id  99  A       
Edit Edit   Drop Drop   id  BTREE   No  No  id  99  A       
nama_barang 99  A   
merk    99  A   
kategori    99  A

Have you tried going from MyISAM type of tables over to Innodb? That would help the load quite alot. Do a copy of the table and convert it with phpmyadmin.

Also make sure that you define the rows in the tables that you use for expressions with Index and if possible shorten the value to varchar with a max value.

I have a table with 9 Million records that when we define the index and make sure it is not only a key it will shorten the time of lookup to a very minimum. From 3 minutes to 5 seconds.

INDEX(nama_barang)

so that the JOIN is efficient instead of a repeated table scan.

Stick with InnoDB.

PRIMARY KEY(id)
KEY        (id, ...)

Do not start a secondary key with the column(s) of the PRIMARY KEY. It makes the key useless.