How to set MySQL timeout longer?

Question

SELECT nama_pabrik,
       kode_barang,
       nama_barang,
       bacth,
       tanggal,
       ((max(stok)-sum(masuk))+sum(keluar)) AS stok_awal,
       sum(masuk),
       sum(keluar),
       no_faktur,
       in_out,
       satuan.nama_satuan,
       max(stok),
       alamat_pelanggan,
       alamat_supplier
FROM kartu_barang
INNER JOIN barang ON kartu_barang.id_barang = barang.id_barang
INNER JOIN satuan ON barang.id_satuan = satuan.id_satuan
LEFT JOIN pelanggan ON kartu_barang.in_out = pelanggan.nama_pelanggan
LEFT JOIN supplier ON kartu_barang.in_out = supplier.nama_supplier
LEFT JOIN pabrik ON barang.id_pabrik = pabrik.id_pabrik
WHERE month(tanggal)<= '10'
  AND month(tanggal)>= '12'
GROUP BY kartu_barang.id_barang
ORDER BY nama_barang,
         id_kartu,
         tanggal

i have sql like that.it return until 3000 data. but it take long time and timeout .but if i change where statment to onlu one month not three mont WHERE month(tanggal)== '10' not timeout.

Solution 1

You could increase it in php.ini on the line mysql.connect_timeout = 14400. Also increase the default_socket_timeout = 14400

Note:- that if your PHP setting allow you to do an ini_set, you can also do as follows:-

ini_set('mysql.connect_timeout', 14400);
ini_set('default_socket_timeout', 14400);

Solution 2

If you use this sql in php script then add this line in top of your script

ini_set('max_execution_time', 0);

It will overcome the maximum execution time limit of your script, which is 30 seconds by default.