Databases
StackOverflow2013
Postsdbo
POSlow counting record from MYSQL query with looping INNERJOIN
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSlow counting record from MYSQL query with looping INNERJOIN
    text
    copied!<p>Help, I just want to known count(*) from my database, yess i have many to many relation table with 100.000 - 150.000 record so i use INNER JOIN, it's look like workin for me..</p> <p>BUT....</p> <p>I have a <strong>very slow</strong> query like this, it's work about 2-5second</p> <pre><code>SELECT COUNT(*) FROM monitoring_pasien INNER JOIN master_desa ON master_desa.DESA_ID = monitoring_pasien.DESA_ID INNER JOIN monitoring_loket ON monitoring_pasien.PASIEN_ID = monitoring_loket.PASIEN_ID AND monitoring_pasien.PKM_ID = monitoring_loket.PKM_ID INNER JOIN monitoring_pelayanan ON monitoring_pelayanan.LOKET_ID = monitoring_loket.LOKET_ID AND monitoring_pelayanan.PKM_ID = monitoring_loket.PKM_ID INNER JOIN monitoring_diagnosa ON monitoring_diagnosa.PELAYANAN_ID = monitoring_pelayanan.PELAYANAN_ID AND monitoring_diagnosa.PKM_ID = monitoring_pelayanan.PKM_ID INNER JOIN master_penyakit ON master_penyakit.PENYAKIT_ID = monitoring_diagnosa.PENYAKIT_ID WHERE monitoring_pasien.DESA_ID &lt; 19 AND master_desa.DESA_ID &lt;&gt; 0 AND master_penyakit.SURVEYLANS = '1'; </code></pre> <p>And then... when i used it with this query, it will wok about 100second :(</p> <pre><code>$query = "SELECT * FROM master_desa WHERE DESA_ID &lt; 19 AND DESA_ID &lt;&gt; 0 ORDER BY DESA asc"; $result = mysql_query($query) or die('Error'); while($data = mysql_fetch_array($result)) { $DESA = $data["DESA"]; $DESA_ID = $data["DESA_ID"]; $queryPasien = "SELECT COUNT(*) AS jumPasien FROM monitoring_pasien INNER JOIN master_desa ON master_desa.DESA_ID = monitoring_pasien.DESA_ID INNER JOIN monitoring_loket ON monitoring_pasien.PASIEN_ID = monitoring_loket.PASIEN_ID AND monitoring_pasien.PKM_ID = monitoring_loket.PKM_ID INNER JOIN monitoring_pelayanan ON monitoring_pelayanan.LOKET_ID = monitoring_loket.LOKET_ID AND monitoring_pelayanan.PKM_ID = monitoring_loket.PKM_ID INNER JOIN monitoring_diagnosa ON monitoring_diagnosa.PELAYANAN_ID = monitoring_pelayanan.PELAYANAN_ID AND monitoring_diagnosa.PKM_ID = monitoring_pelayanan.PKM_ID INNER JOIN master_penyakit ON master_penyakit.PENYAKIT_ID = monitoring_diagnosa.PENYAKIT_ID WHERE master_desa.DESA='$DESA' AND master_penyakit.PENYAKIT_ID = '$penyakit' AND TGL LIKE '$pilihan' "; $resultPasien = mysql_query($queryPasien) or die('Error'); while($dataPasien = mysql_fetch_array($resultPasien)) { $jumPasien = $dataPasien['jumPasien']; if ($jumData == 0) { $persen = "0%"; } else { $bagi = $jumPasien/$jumData; $persen = round((float)$bagi * 100 ) . '%'; } echo "&lt;tr&gt; &lt;td&gt;$DESA&lt;/td&gt; &lt;td width=\"40\"&gt; : $persen&lt;/td&gt; &lt;td width=\"70\"&gt;"; $loop = floor($persen/2); $gambar = "images/bar.png"; for($j=0;$j&lt;=$loop;$j++) { echo "&lt;img src=\"$gambar\" alt=\"=\" /&gt;"; } echo "&lt;/td&gt;&lt;/tr&gt;"; } if ($persen &gt;= 0 AND $persen &lt;= 25) { $color = "green"; } else if ($persen &gt; 25 AND $persen &lt;= 50) { $color = "blue"; } else if ($persen &gt; 50 AND $persen &lt;= 75) { $color = "yellow"; } else if ($persen &gt; 75 AND $persen &lt;= 100) { $color = "red"; } if ($DESA == 'GEDONGAN') { $gedongan_jml=" $jumPasien Penderita [ $persen ]"; $gedongan_color=$color;} else if ($DESA == 'BALONGSARI') { $balongsari_jml=" $jumPasien Penderita [ $persen ]"; $balongsari_color=$color;} else if ($DESA == 'BLOOTO') { $blooto_jml=" $jumPasien Penderita [ $persen ]"; $blooto_color=$color;} </code></pre> <p>}</p> <p>whats wrong with my code?</p> <p>Thank's for your help</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload