I'm doing some statistical graphs, which I extract from a table that presents 30,000 records and I will show them in graphics (HIGHCHARTS) ... it turns out that when I make the first query to complete the options of my select, it takes around 20 seconds to load
index.php
<!DOCTYPE html>
<html>
<head>
<title></title>
<script type="text/javascript" src="js/jquery-2.1.3.min.js"></script>
<script src="js/highcharts/js/highcharts.js"></script>
<script src="js/highcharts/js/themes/grid.js"></script>
<script src="js/highcharts/js/modules/exporting.js"></script>
<script type="text/javascript" src="js/funciones.js"></script>
</head>
<body>
<div id="contenedor">
<select name="inc_sem_zon_per" id="inc_sem_zon_per" >
<option value="">Seleccione una opcion</option>
<?php
$con = mysql_connect("localhost","root","");
mysql_select_db("inci",$con);
$consulta_semana= mysql_query("SELECT region FROM registro_tecno group by region");
while($data= mysql_fetch_array($consulta_semana)){
$regionx=$data['region'];
echo "<option value='".$regionx."' $selected>".$regionx."</option>";
}
?>
</select>
<select name="fecha" id="fecha">
<option value="">Seleccione una opcion</option>
<option value="semanas">semana</option>
<option value="meses">mes</option>
</select>
<select name="semanas" id="semanas" hidden>
<option value="">Seleccione:</option>
<option value="TODOS">TODOS</option>
<?php
$con = mysql_connect("localhost","root","");
mysql_select_db("inci",$con);
$consulta_semana= mysql_query("SELECT semana FROM registro_tecno group by semana");
while($data= mysql_fetch_array($consulta_semana)){
$semanax=$data['semana'];
echo "<option value='".$semanax."' $selected>".$semanax."</option>";
}
?>
</select>
<select name="meses" id="meses" hidden>
<option value="">Seleccione:</option>
<option value="TODOS">TODOS</option>
<?php
$con = mysql_connect("localhost","root","");
mysql_select_db("inci",$con);
$consulta_semana= mysql_query("SELECT MONTH(fecha_notificacion) as meses FROM registro_tecno group by MONTH(fecha_notificacion)");
while($data= mysql_fetch_array($consulta_semana)){
$mesx=$data['meses'];
echo "<option value='".$mesx."' $selected>".$mesx."</option>";
}
?>
</select>
<button type="button" class="btn btn-default" onclick="anadir();"><span class="glyphicon glyphicon-search" >BUSCAR</button>
</div>
<div id="container" style="min-width: 310px; height: 400px; margin: 0 auto"></div>
<script >
$( function() {
$("#fecha").change(function() {
if($(this).val()==="meses"){
$("#semanas").prop("hidden",true);
$("#semanas").prop("value=null",true);
$("#meses").prop("hidden",false);
}else{
$("#semanas").prop("hidden",false);
$("#meses").prop("value=null",true);
$("#meses").prop("hidden",true);
}
});
});
</script>
</body>
</html>
then at the moment of pressing the "search" button, I send the values chosen by AJAX, which is done in functions. js
function anadir(){
var parametros={
"inc_sem_zon_per": $("#inc_sem_zon_per").val(),
"fecha": $("#fecha").val(),
"meses": $("#meses").val(),
"semanas": $("#semanas").val(),
}
$.ajax({
data: parametros,
url:"mostrar.php",
type:"POST",
beforeSend: function(){
$("#container").html("Procesando...");
},
success: function(vista){
$("#container").html(vista);
}
});
}
then send the values to show.php, and here my second delay occurs, because when I click on "search" it stays loading exactly one minute, and then shows it on the graph ... my code of mostrar.php shows the following search in the mysql
<?php
$inc_sem_zon_per=$_POST['inc_sem_zon_per'];
$fecha=$_POST['fecha'];
$meses=$_POST['meses'];
$semanas=$_POST['semanas'];
include("conexion.php");
$estado[0]="En Curso";
$estado[1]="Pendiente";
$estado[2]="Resuelto";
if ($fecha=="semanas") {
$semana_max=$con->query("SELECT MAX(semana) as sem_max FROM registro_tecno ");
while($row_sem_max= mysqli_fetch_array($semana_max, MYSQLI_ASSOC))
{
$me_max= $row_sem_max["sem_max"];
}
$limite_inferior=(int)$me_max-(int)$semanas;
for ($i=0; $i <=2 ; $i++) {
$c=0;
$a="";
for ($j=$limite_inferior+1; $j <=$me_max ; $j++) {
$result_inc =$con->query("SELECT count(incidencia) as ver FROM registro_tecno where (estado='".$estado[$i]."' and region='".$inc_sem_zon_per."' and semana=".$j.") GROUP by semana");
$rown3=$result_inc->num_rows;
if ($rown3==0) {
$a=$a."0,";
$c++;
}else{
while($row_inc= mysqli_fetch_array($result_inc, MYSQLI_ASSOC))
{
$inc[$c] = $row_inc["ver"];
$a=$a.$inc[$c].",";
$c++;
}
}
}
$s[$i]=$a;
echo "estado: ".$estado[$i];
echo "<br>ver ".$s[$i]."<br>";
}
mysqli_close($con);
}elseif ($fecha=="meses") {
$c=0;
$acum_asig="";
$m_max=$con->query("SELECT MAX(mes) as mes_max FROM registro_tecno ");
while($row_mes_max= mysqli_fetch_array($m_max, MYSQLI_ASSOC))
{
$me_max= $row_mes_max["mes_max"];
echo "ver: ".$me_max;
}
$limite_inferior=(int)$me_max-(int)$meses;
echo "<br>ver4: ".$limite_inferior;
for ($i=0; $i <=2 ; $i++) {
$c=0;
$a="";
for ($j=$limite_inferior+1; $j <=$me_max ; $j++) {
$result_inc =$con->query("SELECT count(incidencia) as ver FROM registro_tecno where (estado='".$estado[$i]."' and region='".$inc_sem_zon_per."' and mes=".$j.") GROUP by mes");
$rown3=$result_inc->num_rows;
if ($rown3==0) {
$a=$a."0,";
$c++;
}else{
while($row_inc= mysqli_fetch_array($result_inc, MYSQLI_ASSOC))
{
$inc[$c] = $row_inc["ver"];
$a=$a.$inc[$c].",";
$c++;
}
}
}
$s[$i]=$a;
}
mysqli_close($con);
}
?>
<script type="text/javascript">
$(function () {
$('#container').highcharts({
chart: {
type: 'column'
},
title: {
text: 'Monthly Average Rainfall'
},
subtitle: {
text: 'Source: WorldClimate.com'
},
xAxis: {
categories: [
<?php for($y=$limite_inferior+1;$y<=$me_max;$y++){ echo "'MES ".$y."',";}?>
]
},
yAxis: {
min: 0,
title: {
text: 'Rainfall (mm)'
}
},
tooltip: {
headerFormat: '<span style="font-size:10px">{point.key}</span><table>',
pointFormat: '<tr><td style="color:{series.color};padding:0">{series.name}: </td>' +
'<td style="padding:0"><b>{point.y:.1f} mm</b></td></tr>',
footerFormat: '</table>',
shared: true,
useHTML: true
},
plotOptions: {
column: {
pointPadding: 0.2,
borderWidth: 0
}
},
series: [
<?php for($h=0;$h<=2;$h++){?>
{
name: <?php echo "'".$estado[$h]."',"; ?>
data: [
<?php
echo $s[$h];
?>
]
},
<?php }?>
]
});
});
</script>
then the result returns, but what happens is that it takes too long, then my question would be, is there any way to make a query to MYSQL and not take too long?