|

Búsquedas geolocalizadas en WordPress

Este es un ejemplo raro de uso de WordPress, pero el otro día una persona lo preguntaba en el foro de soporte de WordPress. El usuario tenía los posts geolocalizados (latitud/longitud), supongo con postmetas correspondientes.

Para poder realizar este tipo de búsquedas es necesario usar MySQL procedures, claro que no todos los hosting lo permiten:

CREATE PROCEDURE geodist (IN mylat decimal(18,12), IN mylon decimal(18,12), IN dist float)
BEGIN
declare lon1 float;
declare lon2 float;
declare lat1 float;
declare lat2 float;
set lon1 = mylon-dist/abs(cos(radians(mylat))*69);
set lon2 = mylon+dist/abs(cos(radians(mylat))*69);
set lat1 = mylat-(dist/69);
set lat2 = mylat+(dist/69);
SELECT p.*, 3956 * 2 * ASIN(SQRT( POWER(SIN((mylat -lat.meta_value) * pi()/180 / 2), 2) +COS(mylat * pi()/180) * COS(lat.meta_value * pi()/180) *POWER(SIN((mylon - lon.meta_value) * pi()/180 / 2), 2) )) as distance FROM wp_posts as p, wp_postmeta lat, wp_postmeta lon WHERE p.ID = lat.post_id and lat.meta_key = 'latitude' and p.ID = lon.post_id and lon.meta_key = 'longitude' and lon.meta_value between lon1 and lon2 and lat.meta_value between lat1 and lat2
and post_status = 'publish'
having distance < dist ORDER BY Distance;
END

Una vez creado el procedure deberemos llamarlo para obtener los resultados:

global $wpdb;
$posts = $wpdb->query($wpdb->prepare('call geodist(?, ?, ?)', $lat, $lon, $dis));

Acceder al código en Gist

Hacer un split en MySQL

Normalmente no se suelen necesitar hacer splits en MySQL, porque un buen diseño de la BD debería almacenar los campos en distintas columnas, y si no se puede realizar así, se suele guardar la información serializada en PHP o en formato JSON o XML. Pero no siempre son las cosas como queremos y alguna vez nos podemos encontrar con la necesidad de hacer un split de un campo en MySQL (como me ha pasado a mi ahora).

Hacerlo es fácil, solo es necesario hacer dos substrings, por cada campo:

SELECT `ip` ,
SUBSTRING_INDEX( `ip` , '.', 1 ) AS a,
SUBSTRING_INDEX(SUBSTRING_INDEX( `ip` , '.', 2 ),'.',-1) AS b,
SUBSTRING_INDEX(SUBSTRING_INDEX( `ip` , '.', -2 ),'.',1) AS c,
SUBSTRING_INDEX( `ip` , '.', -1 ) AS d
FROM log_table

MySQL String Functions

|

INSERT IGNORE en MySQL

Muchas veces realizamos inserts y nuestras comprobaciones son erróneas e intentamos insertar registros duplicados, dándonos un error gordo en MySQL. Para evitar esto, normalmente se usa un REPLACE cuando la situación permite reemplazar los valores nuevos por los actuales, pero cuando esto no es posible, nos tenemos que pegar con el código buscando la comprobación que no hacemos correctamente. Para evitar esto: insertar algo ya existente, MySQL ofrece la posibilidad de ignorar el insert cuando no se puede insertar:

INSERT IGNORE 
  INTO tabla 
    (id, valor, ...) 
  VALUES 
    (25, 'valor, ...)

Cada día aprendo algo nuevo, aunque a veces deberían darme con la Biblia de MySQL (775 páginas) en la cabeza por no conocer antes esto.

|

BigDump: facilita recuperar dumps de MySQL grandes

A veces es un lío enorme recuperar BDs obtenidos de dumps cuando el fichero es enorme, sobre todo si usamos phpMyAdmin (y no tenemos acceso a él). Por ello este script nos puede venir muy bien para recuperar backups anteriores, ya que recupera de poco en poco y se auto-reinicia él mismo.

BigDump

MySQL 5.5 Milestone 2

MySQL ha sacado la primera versión Milestone (o RC) de la 5.5, la cual trae algunas novedades interesantes:

  • Replicación semisíncrona: la cual se asegura de que al menos un esclavo confirme la transacción, habrá que ver que tal es esto para el rendimiento.
  • Nueva sentencia LOAD XML
  • Particionamiento por rangos y listas, que por cierto, yo desconocía que la versión Community de MySQL admitía particionamiento.
  • Versión mejorada del plugin de InnoDB

What is new in MySQL 5.5

Consejos para MySQL

Una serie de consejos bastante útiles para usar en MySQL, algunos ya conocidos y otros no tanto:

  • Optimiza tus queries para la caché: por ejemplo es preferible crear una condición en el WHERE que sea fecha>’$fecha’ que fecha>now(), ya que indicando que fecha exáctamente, si la query se vuelve a ejecutar puede ser rescatada de la cache.
  • Usa mismo tipo de campos para las JOINs: si los campos son de distinto tipo en las tablas, al hacer el join MySQL no es capaz de usar uno de los índices (suponiendo que estén indexados, claro).
  • No uses ORDER BY RAND(): es preferible obtener una lista de ids aleatorios mediante código y luego obtener los registros por esos IDs.
  • Evita SELECT *: cuantos más campos leas, más información recuperas y más lentas serán las queries.
  • Usa ENUM en vez de VARCHAR: cuando uses un campo para un número limitado de strings es preferible usar ENUM ya que internamente se usa un tinyint.
  • Utiliza PROCEDURE ANALYSE(): comprobará tu tabla y te recomendará cambios para mejorar su optimización.
  • Usa campos de longitud fija: hará que la tabla sea más rápida.

Más consejos interesantes en el post original.

Top 20+ MySQL Best Practices

Vía / @jlantunez

OQGraph: motor MySQL que permite jerarquías

Open Query Graph (OQGraph) es un plugin para MySQL, MariaDB y Drizzle que permite trabajar con jerarquías o grafos en nuestra BD.

Acaba de ser sacada a la luz, pero puede sernos muy útil para desarrollos de redes sociales: cada nodo del grafo es un usuario. También podríamos utilizarlo para estructuras jerarquizadas: padre, hijos, nietos, …

OQGraph

Vía / PHP Freaks

phpMyBackupPro: realiza backups de MySQL

phpMyBackupPro es un sistema que nos permite controlar las backups de nuestra BD MySQL de forma sencilla.

Entre las características que ofrece nos encontramos con:

  • Backups de una o varias BD con datos, sin datos, estrutura de las tablas, …
  • Tres tipos de comprensión: sin comprensión, gzip o zip
  • Backups programables
  • Administración de backups que permite verlas, recuperarlas, descargarlas o borrarlas
  • Backup en servidor FTP o envío por email
  • Modo shell

phpMyBackupPro

Vía / Weblenium

Mostrar el tiempo transcurrido con MySQL

Algo muy común a la hora de realizar una aplicación web es indicar las fechas según el tiempo transcurrido, en vez de poner 2009-06-03 poner “hace 7 días”.

Para aquellos que necesiten mostrar las fechas de esta forma usando MySQL es posible hacerlo usando una función:

DELIMITER //
CREATE FUNCTION TimeDiffUnits (old DATETIME, new DATETIME) RETURNS CHAR(50) DETERMINISTIC NO SQL
BEGIN
DECLARE diff INTEGER;
SET diff = UNIX_TIMESTAMP(new) - UNIX_TIMESTAMP(old);
CASE
WHEN (diff < 3600) THEN
RETURN CONCAT(FLOOR(diff / 60) , ' Minutes');
WHEN (diff < 86400) THEN
RETURN CONCAT(FLOOR(diff / 3600), ' Hours');
WHEN (diff < 604800) THEN
RETURN CONCAT(FLOOR(diff / 86400), ' Days');
WHEN (diff < 2592000) THEN
RETURN CONCAT(FLOOR(diff / 604800), ' Weeks');
WHEN (diff < 31536000) THEN
RETURN CONCAT(FLOOR(diff / 2592000), ' Months');
ELSE
RETURN CONCAT(FLOOR(diff / 31536000), ' Years');
END CASE;
END //
DELIMITER ;

Importante: no uséis jamás está función ni en el WHERE ni en el ORDER BY ni en el GROUP BY, ya que se ejecutaría para cada registro de las tablas.

User Friendly Age Function for MySQL