Optimización de consultas en MySQL

en por Donnie Rocken Informática, SQL1 comentario

Vamos con una serie de consejos a la hora de optimizar consultas en MySQL:

  • No uses el comodín * si no quieres recuperar todos los datos. Estarás aumentando innecesariamente el volumen de datos.
  • En selects, updates y deletes utiliza LIMIT 1 cuando sólo esperes un resultado. De esa forma cuando lo encuentre parará, y no recorrerá toda la tabla. Esto además te valdrá como medida de seguridad, ya que un pudate o delete mal hecho puede joderte una tabla y empantanarte en una recuperación de datos.
  • En las cláusulas where no utilices paréntesis innecesarios.
  • Si al hacer una consulta con GROUP BY o DISTINCT sabes que el resultado va a ser reducido usa la opción SQL_SMALL_RESULT, que usará tablas temporales dándote mayor velocidad en el acceso a datos.
  • COUNT(*) sólo se debe utilizar en consultas sin WHERE y que afecten a sólo una tabla.
  • En los selects que impliquen varias tablas utiliza alias y ponlos antes de los campos. Ahorrarás tiempo al no tener que buscar la tabla a la que pertenece.
  • Para unir tablas no uses un where. Funciona, pero ahí el motor hace el producto cartesiano de todas las tablas implicadas y luego lo filtra. INNER JOIN, LEFT JOIN o RIGHT JOIN son tus fieles amigos. Si los usas las tablas se irán uniendo a medida que las declaras
  • Usa columnas NOT NULL si es posible. Las columnas NULL requieren de un bit extra.
  • Para ordenación aleatoria no uses ORDER BY RAND(). En ese mismo blog ya dimos opciones alternativas
  • Analiza tus consultas con EXPLAIN. Si pones EXPLAIN antes de tu consulta recibirás una explicación de cómo se realiza esta en el servidor. Esto te permitirá ver formas de optimizarla.
  • A la hora de relacionar tablas usa siempre campos que estén indizados, ganarás en velocidad.
  • Optimiza tus índices. Ten en cuenta que suelen agilizar las lecturas pero ralentizar las inserciones (bueno, lo de ralentizar es en Microsoft SQL-Server, debería mirar sin en MySQL también ocurre eso). Aquí tienes un buen artículo sobre el tema (aunque puede que dentro de poco haga yo uno)

En fin, una serie de consejillos para mejorar el rendimiento de vuestra base de datos. Por cierto, y a modo anecdótico. Hace años un ingenieron me dijo que no usara LIMIT 1 porque era «meter más consultas que no van a hacer nada porque estás buscando sobre un ID»… todavía no he encontrado a nadie más que defienda esa teoría (igual era ingeniero agrónomo y no informático, eso explicaría mucho).

Insert IF NOT EXISTS en MySQL

en por Donnie Rocken SQL4 comentarios

Muchas veces necesitamos crear una entrada en una tabla sólo en el caso de que no exista ya. Por desgracia no existe una sintaxis de INSERT IF NOT EXISTS, como sí existe por ejemplo con CREATE. Os estaréis cagando en mi porque el título os dio esperanzas… Tranquilos, existe una simple alternativa, haciendo uso de SELECT FROM dual con una condición WHERE NOT EXISTS:

Veamos un ejemplo, y debajo lo explico. Vamos a insertar un pueblo en una tabla sólo si no ha sido insertado ya:

INSERT INTO poblaciones(Nombre)
SELECT 'Bergantiños'
FROM dual
WHERE NOT EXISTS (SELECT Nombre FROM poblaciones WHERE Nombre = ‘Bergantiños’)LIMIT 1

Como veis usamos SELECT FROM dual, un nombre de tabla que se especifica en MySQL, Oracle o PostgreSQL como nombre de tabla falsa en asignaciones en las que no usamos tablas realmente. De esta forma podemos usar la condición WHERE NOT EXISTS para comprobar con una búsqueda que el campo no está en la tabla. En la búsqueda uso LIMIT 1 para que pare en cuanto encuentre un resultado y reducir la carga de trabajo.

(Editado, y gracias a infocat20, que me avisó de que tenía el limit mal colocado)

Bucles y condicionales en procedimientos almacenados de MySQL

en por Donnie Rocken SQL7 comentarios

A la hora de crear procedimientos almacenados en MySQL tenemos la opción de utilizar distintos tipos de bucles y de condicionales, lo que nos dotará de la capacidad de llevar a cabo acciones más complejas dentro de nuestros procedimientos.

Para esto disponemos de las estructuras de control IF THEN ELSE, CASE, WHILE y REPEAT, además de poder definir y declarar variables locales dentro de los procedimientos.

Variables:
Para declarar una variable la definimos con DEFINE y la inicializamos con SET:

DEFINE varprueba INT; #definimos la variable y su tipo
SET varpruebra = 10; #le damos un valor concordante con el tipo

IF THEN ELSE:
El condicional simple IF que tenemos en la mayoría de los lenguajes de programación, que puede ser complementado o no con un ELSE para el caso de que no se cumpla. La sintaxis es IF condición THEN (el ELSE si hace falta) END IF. Podéis ver un ejemplo:

delimiter $$
CREATE procedure ej(IN val int)     /* Parámetro de entrada */
  begin    
    IF val = 0 then
      INSERT INTO tbejemplo VALUES('No disponible');
    else
      INSERT INTO tbejemplo VALUES(val);
    end IF;
  end$$
delimiter ;

CASE:
Condicional que nos permite evaluar el valor de una variable y realizar diversas acciones según varias posibilidades. Es una forma de abreviar un montón de IF anidados. En este caso el ELSE nos permite definir un valor por defecto (como el default en un switch/case de C, Java o PHP):

delimiter $$
CREATE procedure ej(IN val int)     /* Parámetro de entrada */
  begin
    case val
     when 5 then INSERT INTO prueba VALUES('Suficientes');
     when 4 then INSERT INTO prueba VALUES('Reserva');
     else INSERT INTO prueba VALUES ('Insuficientes);
    end case;
  end$$
delimiter;

WHILE:
Un bucle que hace que un procedimiento se repita mientras se de una condición. Si la condición ya no se da antes de entrar al bucle, no entra.

delimiter $$
CREATE procedure ej(IN val int)     /* Parámetro de entrada */
  begin
    define i int;
    set i = 0;
    while i<5 do
      INSERT INTO prueba VALUES (i);
      set i=i+1;
    end while;
  end$$
delimiter ;

REPEAT:
Lo contratio que WHILE, repeat es un bucle UNTIL, se realiza hasta que se de una condición. Si bien también comparte con el DO…WHILE de C (y Java, y PHP…) que siempre se ejecuta al menos una vez.

delimiter $$
CREATE procedure ej(IN val int)     /* Parámetro de entrada */
  begin
    define i int;
    set i = 0;
    repeat
      INSERT INTO prueba VALUES (i);
      set i=i+1;
      until i <= 5;
    end repeat;
  end$$
delimiter ;

Con esto ya podéis empezar a programar un poco más en serio dentro de MySQL.

DonnieTabs, plugin simple de pestañas para jQuery

en por Donnie Rocken Informática, Javascript/jquery2 comentarios

El plugin DonnieTabs, a cargo del menda, está disponible con licencia MIT en GitHub para que lo utilicéis, modifiquéis y forkeéis a vuestro gusto.

Su uso es simple:

Añades en tu web la librería jQuery, el archivo .js de DonnieTabs y el archivo CSS (y si necesitas el CSS extra para IE también viene).

Luego para crear las pestañas y el contenido simplemente tenéis que usar unas clases y nomenclatura concretos en el marcado HTML y punto:

<!-- Para las pestañas una lista y dentro cada elemento así-->
<ul class="simpletabs">
    <li>
	<a href="#tab1">Tab1</a>
    </li>
    <li>
	<a href="#tab2">Tab2</a>
    </li>
    <li>
	<a href="#tab3">Third tab</a>
    </li>						
</ul>
<!-- La lista debe ser la clase SimpleTabs, y la referencia del enlace debe ser al Id de la pestaña de contenido-->

Con eso tenéis las pestañas de navegación, la parte del contenido irá así:

<!-- Todo el contenido irá dentro un div de la clase tab_container -->
<div class="tab_container">
<!-- Y dentro, el contenido va en divs de la clase tab_content-->
    <div id="tab1" class="tab_content">
	<h3>titulo</h3>
	<p>Contenido</p>	
    </div>
    <div id="tab2" class="tab_content">
	<h3>titulo</h3>
	<p>Contenido</p>
    </div>
    <div id="tab3" class="tab_content">
	<h3>titulo</h3>
	<p>Contenido</p>
    </div>			
</div>

Con esto ya tenéis vuestra página con navegación por pestañas. Podéis aportar todo lo que queráis a este proyecto. Yo intentaré meterle más cosas, por ahora es algo muy básico y ya tengo alguna idea.

PHPMailer en gallego

en por Donnie Rocken Informática, Lenguajes de programaciónDeja un comentario

Español:

Si anteayer os comenté que había hecho una traducción al gallego de Colorbox que ya se había integrado en el proyecto en GitHub hoy toca comentar lo mismo de PHPMailer, clase para el envío de correos desde PHP. Una pequeña contribución por mi parte a proyectos libres de los que he echado mano en más de un desarrollo.

Galego:

Se antonte comentéi que fixera unha traducción ó galego do Colorbox que xa fora integrada no proxecto de GitHub hoxe toca facelo con PHPMailer, a popular clase para o envío de correos dende PHP. Unha pequena contribución pola miña parte a proxectos libres dos que ben deitei máis dunha vez nos meus desenvolvementos.

Colorbox: Plugin de jQuery para ventanas modales y lightbox, ahora en gallego

en por Donnie Rocken Javascript/jquery2 comentarios

Español:

Soy habitual usuario de Colorbox, plugin que he utilizado en varios desarrollos (Loterías Cedeira, Lana y Punto) para la creación de ventanas modales con contenido externo o con galerías de fotos, una alternativa a las ventanas modales de jQueryUi o a Lightbox. Desde hoy además incluye traducción al gallego dentro de los complementos de internalización (gracias al menda, que vale que no era mucho pero nadie lo había hecho). Podéis descargarlo desde este enlace.

Galego:

Son un devoto usuario de Colorbox , plugin que xa utilicéi en varios desenvolvementos (Loterías Cedeira, Lana Y Punto) para xerar fiestras modáis con contido externo ou con galerías de imaxes, unha alternativa ás fiestras modáis de jQueryUi ou ó Lightbox. Dende hoxe además inclúe a traducción ó galego dentro dos seus complementos de internacionalización (grazas a min, que vale que non era moito choio pero ninguén o fixera antes). Podedes descargalo dende esta ligazón.

Plugins de subida múltiple de archivos con jQuery

en por Donnie Rocken Informática, Javascript/jqueryDeja un comentario

Si bien HTML5 ya incluye un campo «multi» para subir múltiples archivos de una sola vez los navegadores tienden a ponerse puñeteros con el tema, así que de momento Javascript es la mejor solución. Estos días me he visto en la necesidad de recurrir a uno de estos plugins, y he encontrado cosas interesantes:

Como estética ninguno gana a jQuery File Upload, integadro tanto con Bootstrap como con jQueryUi, con una presentación sumamente atractiva. Eso sí, tendrás que pasar por caja porque no se trata de un plugin libre (lo que en mi caso me llevó a descartarlo). Tres cuartos de lo mismo con FineUploader, muy chulo pero tendrás que pagar.

Si nos vamos al campo de lo libre, tenemos Plupload bajo licencia GPLv2, un plugin que permite incluso meter elementos Flash, Silverlight y HTML5 juntos para que funcionen como «fallback» en caso de que el navegador no soporte alguno.

Y finalmente, por cuestiones de flexibilidad, acabé por decantarme por Multiple File Upload Plugin, que está claro que no es el más bonito, pero sí ligero, xbrowser y flexible. Fácilmente personalizable tanto en temas de traducciones como a la hora de trabajar con la subida o de modificar su apariencia.

Estas son las que me han parecido las mejores opciones a la hora de trabajar con subida múltiple, entre lo que he podido ver. Ahora cada cual que busque, pruebe y elija (Wood).

Comprobar si un número es par o impar con PHP

en por Donnie Rocken Informática, Lenguajes de programaciónDeja un comentario

El otro día me preguntaron si había alguna función de PHP que devolviera si un número es par o impar, tipo is_odd, is_even o similar. Realmente es una comprobación que puedes hacer con una línea de código, así que no existe una función que lo haga. Si lo que quieres es simplemente imprimir en pantalla par e impar la solución es simple (supongamos que el número a comprobar está en la variable $number):

echo ($number % 2 ? 'Impar' : 'Par');

Tan solo necesitas la operación de módulo, que devuelve el resto de la división entera del número entre 2. Como el resultado en caso de que sea par será 0 este será interpretado en los condicionales o en un operador ternario como un FALSE.

¿Lo queréis como función que devuelva true o false? Ok, es simple. Esta es una función que devuelve TRUE si es impar y false si es par :

function esImpar($number){
    return $number % 2;
}

Y si te quieres ir de guay hasta puedes usar el operador & a nivel de bit para hacerlo (para quedar más pro)

if($number & 1){  
    echo 'Impar';  
}else{  
    echo 'Par';  
}

Ale, ya tenéis por ahí tres soluciones para comprobar si un número es par o impar.

Función PHP para hash de contraseñas con GOST y salt

en por Donnie Rocken Informática, Lenguajes de programación5 comentarios

Se está poniendo de moda GOST (así, sin H, que no es inglés) desde todo el asunto Snowden porque, según han empezado a decir algunos expertos en seguridad, es uno de los algoritmos que la NSA no ha logrado romper (al menos no se sospecha que lo lograran). Se trata de un algoritmo criptográfico parido por matemáticos soviéticos en los últimos años de la Guerra Fría, revisado posteriormente un par de ocasiones. Para más info, como siempre, la wikipedia.

El caso es que con el tema de la paranoia de la seguridad me han pedido una función para encriptar passwords segura en PHP, aunque en PHP 5.5 ya tengamos unas muy cómoda y seguras funciones para trabajar con passwords. Pero cliente manda, y ha leído que GOST tralarí y no se fía de algo que sea standar… así que me he hecho una función propia, que os comparto por si queréis usarla. 

<?
function getSecurePass($password_plano, $username){
    if(strlen($password_plano)<8){
	//exigimos un mínimo de 8 caracteres
        $response = array("aceptado"=>FALSE, "Resultado"=>"Error: Contraseña demasiado corta");
    }else{	
                if(strlen($password_plano) % 2){
                     $salt0="@fr!87$"; //aquí metemos una cadena, la que vosotros prefiráis
                }else{
                     $salt0="~m¿0kL" //aquí metemos otra, y según sea par o impar la longitud cogerá la que corresponda
                }		
		$salt1 = substr($password_plano, strlen($texto_plano)-6, 5);
		$salt2 = substr(md5($username), 6, 6);
		$salt3 = substr($username, strlen($username),-2);
		$arrayPss = str_split($password_plano,(strlen($password_plano)/2)+1);
		$hash = hash('gost', $salt3.$arrayPss[0].$salt0.$salt1.$arrayPss[1].$salt3.$salt2);
		sleep(1);
		$response = array("aceptado"=>True, "Resultado"=>$hash);
	}
    reutrn $response;
}

?>

Y para complementar el sistema de login contra ataques por fuerza bruta es una buena idea ralentizar la respuesta desde el servidor. ¿Y cómo hacerlo sin darle la vara al usuario mucho? Haciendo una función de login que espere, pero sólo cuando el password sea erróneo. En este caso no desarrollé nada y copié un ejemplo desde php.net que ahí os pego (en mi caso lo adapté para usarlo con el resto del sistema de login desarrollado, en los comentarios del enlace está la función original son su explicación):

<?php
public function handle_login() {
    if($uid = user::check_password($_REQUEST['email'], $_REQUEST['password'])) {
        return self::authenticate_user($uid);
    }
    else {
        // delay failed output by 2 seconds
        // to prevent bruit force attacks
        sleep(2);
        return self::login_failed();
    }
}
?>

Espero que os sirva de ayuda a la hora de asegurar vuestras contraseñas.

Poblar un combo dinámicamente con jQuery y JSON

en por Donnie Rocken Informática, Javascript/jquery, Lenguajes de programación, SQL2 comentarios

Es habitual que, creando formularios, nos encontremos con la situación de tener dos combos (o campo select si lo preferís) y que uno tenga que cargar/modificar sus datos según el resultado seleccionado en el otro, de forma dinámica. Esto es posible con Javascript, y muy cómodo si utilizamos JSON y jQuery.

Os planteo un ejemplo simple: tenemos dos combos, uno con provincias y otro con ayuntamientos. El marcado HTML va a ser más o menos tal que así:

<select id="provincias" name="provincias">
  <option value=""></option>
  <option value="1">A Coruña</option>
  <option value="2">Lugo</option>
  <option value="3">Ourense</option>
  <option value="4">Pontevedra</option>
</select>
<select id="poblaciones" name="poblaciones" disabled="disabled">
</select>

Como véis, al momento de cargar la página el campo provincias tendrá las cuatro provincias gallegas, y el campo poblaciones estará desactivado. ¿Ahora qué necesitamos? Pues primero necesitamos un script que nos saque las poblaciones de la base de datos y nos las envíe como un JSON. Cualquier lenguaje de lado del servidor nos vale, para el ejemplo va a ser PHP (pero vamos, que podría aplicarse con Java, Ruby, node.js, Python…). Crearemos un script llamado getPoblacionesJson.php

<?php
include 'conexionbd.php';
if ($mysqli -> multi_query("CALL sp_GetPoblaciones(" . $_GET['pr'] . ")")) {
	$poblaciones = array();
	do {
		if ($result = $mysqli -> store_result()) {
			while ($fila = $result -> fetch_assoc()) {				
				$poblaciones[$fila['Id']] = $fila['Nombre'];
			}
		}
	} while($mysqli->next_result());
	print_r(json_encode($poblaciones));
}
?>

En este caso veis que lo que hacemos es llamar a un procedimiento almacenado que nos devuelve las provincias, recorremos el resultado y vamos guardándolo en un array. Finalmente lo convertimos a JSON y lo imprimios para que lo recoja la función de Javascript. Si os estáis preguntando cómo va el procedimiento almacenado, es una simple select en MySQL, tal que así:

DELIMITER $$
CREATE PROCEDURE sp_GetPoblaciones(IN provincia int)
begin
	SELECT Id, Nombre FROM poblaciones WHERE (provincia is null or IdProvincia = provincia) ORDER BY Nombre;
end $$
DELIMITER ;

Ok, tenemos entonces el script del servidor, el marcado y el procedimiento en la base de datos. ¿Qué nos queda? Pues el Javascript, vitaminado con jQuery para ganar productividad:

$("#provincias").change(function() {
	$("#poblaciones").empty();
	$.getJSON('http://localhost/getPoblacionesJson.php?pr='+$("#provincias").val(),function(data){
		console.log(JSON.stringify(data));
		$.each(data, function(k,v){
			$("#poblaciones").append("<option value=\""+k+"\">"+v+"</option>");
		}).removeAttr("disabled");
	});
});

La idea es simple: Si se registra algún cambio en el combo de provincias vaciamos el combo de poblaciones y lo rellenamos con los nuevos datos, obtenidos mediante la función getJSON() y que recorreremos con each() como un conjunto de claves/valores. Finalmente, por si está desactivado, lo reactivamos. He hecho un console.log por si queréis ver cómo funciona la cosa en la consola de javascript de Chrome o del Firebug.

Espero que os sirva de ayuda esta entrada para trabajar con combos dinámicos.