Optimización Consultas

namorha · #1 (**permalink**) 02/06/2008, 15:49

Hola de nuevo, amigos foreros, buscando por internet, he encontrado varias formas de optimizar mi consultas y he observado que mi manera de hacerlas es la más lenta,

.
He trasteado y no soy capaz de mejorarlas, porque creo q la única forma sería programarlo todo de nuevo, y eso ..... os imaginaís, jajaja.

Esta es mi consultas:

Código PHP:

  $query = ("SELECT * FROM medias WHERE (media3 <'$alto' AND media3 >'$bajo') AND (numplaca='$mono' OR numplaca='$poli' OR numplaca='$cis')  ORDER BY hora");

     $result = @mysql_query($query);

      if (!$result) {

        echo("<p>1-Error performing query $query, error: " . mysql_error() . "</p>");

        exit();

      }

while ( $row = mysql_fetch_array($result) ) {

    echo" face=arial size=2 color=#000000>" . $row["fecha"]);

}

Como podría optimizarla???

Gracias y saludos.

Tokkara · #2 (**permalink**) 02/06/2008, 16:02

No se porque. Pero tu sentencia es bastante corriente y no esta mal. El unico fallo que veo es en el SELECT que extraes todas las columnas cuando solo necesitas una. El where tambien se podria mejora algo, A ver que te parece esta:

Código PHP:

  "SELECT fecha FROM medias WHERE media3 <'$alto' AND media3 >'$bajo' AND numplaca IN ('$mono', '$poli', '$cis') ORDER BY hora"

Saludos.

namorha · #3 (**permalink**) 02/06/2008, 16:16

Hola, gracias por la correción, funciona perfecto pero no me acelera la consulta, de todas formas la use.

Pongo * porque en realidad necesito todas las columnas lo que pasa es q no copie todo pq era muy largo.

El problemas, es q tengo una BD de unos 2Gigas y es una locura, me salta hasta un error por la duración de la consulta y es que no se como solucionarlo.

Saludos

terepaima · #4 (**permalink**) 02/06/2008, 16:18

Hola

Tienes que pensar en indexar esa tabla amigo

Tokkara · #5 (**permalink**) 02/06/2008, 16:23

Puedes probar hacer las extracciones a trozos usando la clausula LIMIT.

Código PHP:

  "SELECT fecha FROM medias WHERE media3 <'$alto' AND media3 >'$bajo' AND numplaca IN ('$mono', '$poli', '$cis') ORDER BY hora LIMIT '$principio', 100"

por ejemplo.

Si es por el tiempo tambien podrías modificar el tiempo maximo de ejecucion de scripts ( si tu server lo permite );

Código PHP:

  set_time_limit(0)  //No tienes limite de expiracion de tu script.

Saludos

namorha · #6 (**permalink**) 02/06/2008, 16:27

Cita:

Iniciado por Tokkara

Puedes probar hacer las extracciones a trozos usando la clausula LIMIT.

Código PHP:

  "SELECT fecha FROM medias WHERE media3 <'$alto' AND media3 >'$bajo' AND numplaca IN ('$mono', '$poli', '$cis') ORDER BY hora LIMIT '$principio', 100"

por ejemplo.

Si es por el tiempo tambien podrías modificar el tiempo maximo de ejecucion de scripts ( si tu server lo permite );

Código PHP:

  set_time_limit(0)  //No tienes limite de expiracion de tu script.

Saludos

Hola, que sería la variable $principio. Saludos

namorha · #7 (**permalink**) 02/06/2008, 16:28

Cita:

Iniciado por terepaima

Hola

Tienes que pensar en indexar esa tabla amigo

Hola, voy a trastear lo de indexar y te cuento. Gracias.

Tokkara · #8 (**permalink**) 02/06/2008, 16:46

Cita:

Iniciado por namorha

Hola, que sería la variable $principio. Saludos

Perdon, con las prisas se me olivido explicar esta parte. Con la clausula LIMIT puedes ir partiendo los resultados haciendo que los devuelva por trozos.

Código PHP:

  $tmno = 100

$principio =(int)$_GET["pag"]*$tmno;

 
$query = "SELECT fecha FROM medias WHERE media3 <'$alto' AND media3 >'$bajo' AND numplaca IN ('$mono', '$poli', '$cis') ORDER BY hora LIMIT '$princpio',  '$tmno'";

     $result = @mysql_query($query);

      if (!$result) {

        echo("<p>1-Error performing query $query, error: " . mysql_error() . "</p>");

        exit();

      }

while ( $row = mysql_fetch_array($result) ) {

    echo" face=arial size=2 color=#000000>" . $row["fecha"]);

}

 
 
echo "<a href=\"".$_SERVER["PHP_SELF"]."?pag=".$i++."\">Siguiente</a>

Con esto te creas un pequeño script que te extraes las 100 primeras filas y al final te escribe un enlace que al pulsarlo te vuelve a ejecutar el script con las siguientes 100 filas. (Lo he escrito sobre la marcha, asi que puede que tenga algun error de sintaxis).

Como han dicho tambien puedes probar a indexarla o a optimizarla usando las sentencias de MYSQL ( te pondria algun enlaces para que lo viera pero todavia no puedo).
Saludos

namorha · #9 (**permalink**) 03/06/2008, 03:36

Hola Tokkara, es muy buena idea, me suelta un error, pero lo probe a pelo y me acelera mucho las consultas, lo que no me deja es paginar, que sería lo idóneo.

El error me lo produce en:

Código PHP:

  $principio =(int)$_GET["pag"]*$tmno; //Parse error: syntax error, unexpected T_VARIABLE

Como podría solucionarlo ???

Gracias y saludos.

Tokkara · #10 (**permalink**) 03/06/2008, 03:45

Perdon habia pequeños errores, lo copie directamente sobre el post sin probarlo ni nada.

Código PHP:

  $tmno = 100; //faltaba el punto y coma al final de la linea
$principio =(int)($_GET["pag"]*$tmno); //Pon la expresion entre parentesis por si acaso

$query = "SELECT fecha FROM medias WHERE media3 <'$alto' AND media3 >'$bajo' AND numplaca IN ('$mono', '$poli', '$cis') ORDER BY hora LIMIT '$princpio',  '$tmno'";
     $result = @mysql_query($query);
      if (!$result) {
        echo("<p>1-Error performing query $query, error: " . mysql_error() . "</p>");
        exit();
      }
while ( $row = mysql_fetch_array($result) ) {
    echo" face=arial size=2 color=#000000>" . $row["fecha"]);
}


echo "<a href=\"".$_SERVER["PHP_SELF"]."?pag=".$principio++."\">Siguiente</a>

Fijate que tambien he modificado la ultima linea porque i++ no tenia valor asignado.

namorha · #11 (**permalink**) 03/06/2008, 03:58

Hola de nuevo, mira lo que me dice ahora:

-Error performing query SELECT fecha,hora,placa,modelo,potencia,media3,media4,tab la FROM medias WHERE media3 <'' AND media3 >'' AND numplaca IN ('', '', '') ORDER BY hora LIMIT '','100', error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''','100'' at line 1

Ni idea, jajajaja

Esto podría ser porque los valores de la busqueda los introdice el usuario mediante un <form> html y me realiza la búsqueda antes ???

Saludos.

namorha · #12 (**permalink**) 03/06/2008, 04:05

Te pongo el código entero y así lo puedes ver mejor.

Código PHP:

  
<html>

<head>

<title> Consulta por Irradiancias Medias </title>

<script language="JavaScript">

function Abrir_ventana (pagina) {

var opciones="toolbar=no, location=no, directories=no, status=no, menubar=no, scrollbars=no, resizable=yes, width=950, height=420, top=85, left=140";

window.open(pagina,"",opciones);

}

</script>

 
</head>

<body>

    <?php //Función que realiza la consulta de la base de datos mediante intervalos de medias de temperatura.

      //Incluimos librerias necesarias para el graficado de funciones

      include "../libchart/classes/libchart.php";

      //Reutilización de código.

      echo "<center>";include ("menu.php");  echo "</center>";

      //Conexiones de BD      

      $dbcnx = @mysql_connect("localhost", "root", "123456");

      if (!$dbcnx) {

        echo( "<p>No es posible establecer conexión " .

              "con el servidor. Inténtelo más tarde.</p>" );

        exit();

      }

    

    

      if (! @mysql_select_db("meteo") ) {

        echo( "<p>No es posible conectar con la base de datos.</p>" );

        exit();

      }

    

    ?>

<center>

    <p><font face=arial size="2" color=#000000>Introduzca panel/es Fotovoltáico/s a consultar:</font></p>

 
    <form action="<?=$PHP_SELF?>" method="post">

    <input type="checkbox" name="mono" value="1" checked><font face="arial" size="2" color="#000000">Monocristalino</font>

    <input type="checkbox" name="poli" value="2" ><font face="arial" size="2" color="#000000">Policristalino</font>

    <input type="checkbox" name="cis" value="3"><font face="arial" size="2" color="#000000">CIS </font></br></br>

    <p><font face=arial size="2" color=#000000>Introduzca rango de irradiancias a consultar:</font></p>

    <select name="bajo">

    

    <option value="1900">1900 W/m2</option>

    <option value="1800">1800 W/m2</option>

    <option value="1700">1700 W/m2</option>

    <option value="1600">1600 W/m2</option>

    <option value="1500">1500 W/m2</option>

    <option value="1400">1400 W/m2</option>

    <option value="1300">1300 W/m2</option>

    <option value="1200">1200 W/m2</option>

    <option value="1100">1100 W/m2</option>

    <option value="1000">1000 W/m2</option>

    <option value="900">900 W/m2</option>

    <option value="800">800 W/m2</option>

    <option value="700">700 W/m2</option>

    <option value="600">600 W/m2</option>  

    <option value="500">500 W/m2</option>

    <option value="400">400 W/m2</option>

    <option value="300">300 W/m2</option>

    <option value="200">200 W/m2</option>

    <option value="100">100 W/m2</option>

 
    </select>    

    

    <select name="alto">

    

    <option value="2000">2000 W/m2</option>

    <option value="1900">1900 W/m2</option>

    <option value="1800">1800 W/m2</option>

    <option value="1700">1700 W/m2</option>

    <option value="1600">1600 W/m2</option>

    <option value="1500">1500 W/m2</option>

    <option value="1400">1400 W/m2</option>

    <option value="1300">1300 W/m2</option>

    <option value="1200">1200 W/m2</option>

    <option value="1100">1100 W/m2</option>

    <option value="1000">1000 W/m2</option>

    <option value="900">900 W/m2</option>

    <option value="800">800 W/m2</option>

    <option value="700">700 W/m2</option>

    <option value="600">600 W/m2</option>  

    <option value="500">500 W/m2</option>

    <option value="400">400 W/m2</option>

    <option value="300">300 W/m2</option>

    <option value="200">200 W/m2</option>    

    </select>

    <p><input type="submit" value="Consultar"/></p> </form></center>

    <?php

    

 
    //Particionamos las consultas para que no tarden en cargar (Optimización)

    $tmno = 10; 

    $principio =(int)($_GET["pag"]*$tmno); 

 
    //Realizamos consulta BD

     $query = ("SELECT fecha,hora,placa,modelo,potencia,media3,media4,tabla FROM medias WHERE media3 <'$alto' AND media3 >'$bajo' AND numplaca IN ('$mono', '$poli', '$cis') ORDER BY hora LIMIT '$princpio','$tmno'");

     $result = @mysql_query($query);

      if (!$result) {

        echo("<p>1-Error performing query $query, error: " . mysql_error() . "</p>");

        exit();

      }

      echo "<center><table border=1 bordercolor=#E7E7E7 >\n<br>";    

                echo ("<tr bgcolor=#72b9de>\n 

                <center>

                <th><font face=arial size=2 color=#000000> &nbsp;Día medición&nbsp; </font></th>\n

                <th><font face=arial size=2 color=#000000> &nbsp;Hora medición&nbsp; </font></th>\n

                <th><font face=arial size=2 color=#000000>  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Módulo&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></th>\n

                <th><font face=arial size=2 color=#000000> &nbsp;Número Serie&nbsp; </font></th>\n

                <th><font face=arial size=2 color=#000000> &nbsp;Potencia Máxima&nbsp; </font></th>\n

                <th><font face=arial size=2 color=#000000> &nbsp;Irradiancia media&nbsp; </font></th>\n

                <th><font face=arial size=2 color=#000000> &nbsp;Tempertura media&nbsp; </font></th>\n

                <th><font face=arial size=2 color=#000000> &nbsp;Representación&nbsp; </font></th>\n

                </center>

            </tr>\n");

        //Representación de los datos consultados

        while ( $row = mysql_fetch_array($result) ) {

            //Generamos nombre para la imagen

            $horagrafica = $row["hora"];

            $tabladia=$row["tabla"];

            $tabladia_1=$row["fecha"];

            

            echo ("\t<tr bgcolor=#72b9de>\n

            <td><center><font face=arial size=2 color=#000000>" . $row["fecha"]."</font></center></td>\n

            <td><center><font face=arial size=2 color=#000000>" . $row["hora"]."</font></center></td>\n

            <td><center><font face=arial size=2 color=#000000>" . $row["placa"]."</font></center></td>\n

            <td><center><font face=arial size=2 color=#000000>" . $row["modelo"]."</font></center></td>\n

            <td><center><font face=arial size=2 color=#000000>" . $row["potencia"]."</font></center></td>\n

            <td><center><font face=arial size=2 color=#000000>" . $row["media3"]."</font></center></td>\n

            <td><center><font face=arial size=2 color=#000000>" . $row["media4"]."</font></center></td>\n

            <td><center><a href=javascript:Abrir_ventana('generated/grafica".$tabladia.".png')><font face=arial size=2 color=#000000>Graficar</font></a></center></td>\n

         </tr>\n");

        $result_1 = @mysql_query("SELECT * FROM `$tabladia` WHERE voltaje > 0 AND corriente > 0");

          if (!$result_1) {

            echo("<p>2-Error performing query: " . mysql_error() . "</p>");

            exit();

          }

          $chart = new LineChart();

            $dataSet = new XYDataSet();

          $i=0;

          while ( $row_1 = mysql_fetch_array($result_1) ) {    

              $dataSet->addPoint(new Point($row_1[4], $row_1[5]));

              if($i==0){

              $array_1[]="(".$row_1[4].",".$row_1[5].")"; 

              }

               $array_2[]="(".$row_1[4].",".$row_1[5].")"; 

              $i++;

          }

          //Funciones para sacar extraer valores array

          $isc = array_shift ($array_1);

          $voc = array_pop ($array_2);

          $chart->setDataSet($dataSet);

          $chart->setTitle("Gráfica de Potencias (W)  Fecha:".$tabladia_1."  Hora:".$horagrafica." (Eje X: Tensión (V),  Eje Y: Corriente (A)) Isc:".$isc." Voc:".$voc);          

          $chart->render("generated/grafica".$tabladia.".png");

          

    }     

     echo "</table>\n</center>";

    echo "<a href=\"".$_SERVER["PHP_SELF"]."?pag=".$principio++."\">Siguiente</a>"; 

     //Reutilización de código.

     include("tail.php");

    

    ?>

</body>

</html>

Gracias y saludos, espero que me des la solución , porque me estoy quedando loco, jajajajaja.

Tokkara · #13 (**permalink**) 03/06/2008, 04:16

Cita:

Iniciado por namorha

Hola de nuevo, mira lo que me dice ahora:

-Error performing query SELECT fecha,hora,placa,modelo,potencia,media3,media4,tab la FROM medias WHERE media3 <'' AND media3 >'' AND numplaca IN ('', '', '') ORDER BY hora LIMIT '','100', error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''','100'' at line 1

El error esta en la sentencia LIMIT falta un parametro. Si encierras entre comillas simples un 0 creo que mysql lo interpreta como un caracter vacío.

Código PHP:

  $tmno = 100; //faltaba el punto y coma al final de la linea

$principio =(int)($_GET["pag"]*$tmno); //Pon la expresion entre parentesis por si acaso

$limit = " LIMIT ".(empty($principio) ? "0" : $principio).", ".$tmno;

 
$query = "SELECT fecha FROM medias WHERE media3 <'$alto' AND media3 >'$bajo' AND numplaca IN ('$mono', '$poli', '$cis') ORDER BY hora ".$limit;

A ver si ahora va la vencida.

namorha · #14 (**permalink**) 03/06/2008, 05:44

Gracias de ante mano por aguantar mi insistencia, jajaja ....

Ya me hace la consulta de los 100 primeros perfectamente, no me produce error, pero no cuando pulso en siguiente, no realiza o no avanza en la consulta.

????

Gracias y saludos.

Monchiss · #15 (**permalink**) 03/06/2008, 07:20

Hola concuerdo, con terepaima, lo primero que debes hacer es indexar los campos que mas utilizas de cada tabla, o mejor aun, indexa la tabla completa, eso mejorar bastante la velocidad, luego puedes romperte el coco con el codigo! Suerte

namorha · #16 (**permalink**) 03/06/2008, 13:41

Hola, podrías darme algunas directrices para indexar mi tabla ?

Aquí te la dejo, a ver si puedes guiarme.

Código PHP:

  CREATE TABLE `medias` (

  `aid` int(11) NOT NULL auto_increment,

  `fecha` char(50) default NULL,

  `hora` char(50) default NULL,

  `placa` char(50) default NULL,

  `modelo` char(50) default NULL,

  `numplaca` char(50) default NULL,

  `potencia` double default NULL,

  `media3` double default NULL,

  `media4` double default NULL,

  `tabla` char(50) default NULL,

  PRIMARY KEY  (`aid`)

) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=156 ;

Y una vez indexada, las consultas serían igual o distintas, es que he mirado varios tutoriales, y te explican lo que es pero no como es. Toy loko ya!!! Jajaja.

Gracias y saludos

namorha · #17 (**permalink**) 04/06/2008, 02:29

Hola, ya me he creado indices en mi taba:

Código PHP:

  CREATE TABLE `medias` (

  `aid` int(11) NOT NULL auto_increment,

  `fecha` char(50) default NULL,

  `hora` char(50) default NULL,

  `placa` char(50) default NULL,

  `modelo` char(50) default NULL,

  `numplaca` char(50) default NULL,

  `potencia` double default NULL,

  `media3` double default NULL,

  `media4` double default NULL,

  `tabla` char(50) default NULL,

  PRIMARY KEY  (`aid`),

  KEY `indicemedia3` (`media3`),

  KEY `indicemedia4` (`media4`),

  KEY `indicepotencia` (`potencia`),

  KEY `indicefecha` (`fecha`),

  KEY `indicehora` (`hora`)

) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=156 ;

COmo hago ahora las consultas para optimizarlas ???

Gracias y saludos.

namorha · #18 (**permalink**) 04/06/2008, 02:38

Hola, antiguamente hacia las consultas asi:

$result = @mysql_query("SELECT * FROM medias WHERE (hora <'$alto' AND hora >'$bajo') AND (numplaca='$mono' OR numplaca='$poli' OR numplaca='$cis') ORDER BY hora");

Y ahora asi:

$result = @mysql_query("SELECT * FROM medias FORCE INDEX (indicehora)
WHERE (hora <'$alto' AND hora >'$bajo') AND (numplaca='$mono' OR numplaca='$poli' OR numplaca='$cis') ORDER BY hora");

Y me tarda exactamente lo mismo.

Cual es mi fallo??? SAludos

namorha · #19 (**permalink**) 04/06/2008, 15:07

Queridos amigos FOREROS, he encontrado una solución al problema de paginación, y os la muestro por si necesitais usarla algún día.

Navegando por la red y gracias a TARINGA y 'matiasl10' encontre esto:

http://jpinedo.webcindario.com/scripts/paginator/Paginator_v1.6.3.zip

Fuente: http://jpinedo.webcindario.com/scripts/paginator/index.html

Nota: Recomiendo que si haceís la entrada de la consulta dinámica, uses GET en lugar de POST.

Funciona perfecto.

Saludos ......