Devolver XMLs mediante MySQL

ByLuis

Existen bastantes ocasiones en los que el envío de información mediante XML es necesario, por ejemplo web services. Normalmente los datos que necesitamos enviar están almacenados en la base de datos, si necesitamos enviar esos datos en formato XML, normalmente solemos recuperar los datos en formato normal y luego transformarlo a XML mediante nuestro lenguaje de programación (por ejemplo PHP).
En este caso, mediante MySQL y los procedimientos almacenados, podemos crear una consulta que devuelva cada registro en formato XML, aunque habrá que incluirlo entre una etiqueta padre y añadirlo la cabecera XML (<?xml version …).
Las funciones que nos ofrece el autor son tres: xml_escape para remplazar caracteres no permitidos en un XML, xml_attr para crear un atributo XML y xml_tag para crear una etiqueta XML.
XML output from MySql

Similar Posts

Mejora en las consultas anidadas en MySQL6

ByLuis

Buena noticia de rendimiento en la futura versión de MySQL. En este caso se trata de las consultas anidadas dentro de MySQL, pongamos un ejemplo.

Si tenemos esta consulta:

select title, from_date, to_date
from titles
where emp_no in
(select emp_no
from employees
where first_name = 'Mary'
and last_name = 'Sluis'
)

En MySQL5 (usando estos datos) obtendríamos el resultado (1 fila) en 7.88 segundos, mientras que en MySQL6 tardaría 0.13 segundos.

El motivo parece ser que MySQL transforma la subconsulta en un join.

Drizzling MySQL

jHeidi: cliente MySQL multiplataforma

ByLuis

jHeidi es una versión de Heidi realizado en Java para que se pueda ejecutar en otras plataformas y no solo en Windows.

jheidi.png

Entre las características que ofrece nos encontramos con:

  • Navegador de bases de datos, tablas y campos
  • Crear, actualizar y borrar BD, tablas, campos e índices
  • Ventana para queries
  • Administrador de usuarios
  • Mantenimiento de tablas
  • Administrador de conexiones
  • Exportar datos (CSV, HTML, …)
  • Importar CVS

jHeidi

Consejos para una configuración correcta de la arquitectura de MySQL

ByLuis

Más consejos para tener una configuración más adecuada de MySQL, en este caso desde el punto de vista de la arquitectura.

  • Instala MySQL en su propia partición de disco duro.
  • Cada servidor de MySQL debe tener us propio directorio de administración, directorio de logs, scripts, …
  • Situa los ficheros de datos y los ficheros de logs binarios en discos y controladores separados si es posible, nunca tengas los ficheros de datos y los que sirven para recuperarlos en el mismo lugar físico.
  • Haz copia de seguridad de tus ficheros de configuración.
  • Cambia la configuración de tu base de datos poco a poco para ir como se va comportando.
  • Antes de actualizar MySQL asegurate de conocer los cambios de la release y estudia que las aplicaciones sean compatibles con la nueva versión.
  • Comprueba de vez en cuando la recuperación de backups
  • Intenta usar programas para monitorizar y controlar tu MySQL.

The MySQL Optimal Configuration Architecture

|

Búsquedas geolocalizadas en WordPress

ByLuis

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

Consejos MySQL

ByLuis

El problema que tenemos muchos a la hora de desarrollar es no profundizar en las características que nos ofrece una herramienta. En este caso, solemos realizar una serie de consultas en MySQL que se podrían optimizar para que fuera todo más rápido.

Búsqueda de palabras

/* Muy rápida */
SELECT * FROM tabla WHERE MATCH (`campo`) AGAINST ('palabra')
/* Rápida */
SELECT * FROM tabla WHERE MATCH (`campo`) AGAINST ('+palabra' IN BOOLEAN MODE)
/* Lentas */
SELECT * FROM tabla WHERE RLIKE '(^| +)palabra($| +)'
/* o */
SELECT * FROM tabla WHERE
RLIKE '([[:space:]]|[[:<:]])palabra([[:space:]]|[[:>:]])'

Búsquedas contenidas

/* Muy rápida */
SELECT * FROM tabla WHERE MATCH (`campo`) AGAINST ('palabra*' IN BOOLEAN MODE)
/* Rápida */
SELECT * FROM tabla WHERE FIELD LIKE 'palabra%'
/* Lentas */
SELECT * FROM tabla WHERE MATCH (`campo`) AGAINST ('*palabra*' IN BOOLEAN MODE)
/* o */
SELECT * FROM tabla WHERE campo LIKE '%palabra%'

Número de registros

/* Muy rápida */
SELECT SQL_CALC_FOUND_ROWS * FROM tabla WHERE condición LIMIT 0, 10
SELECT FOUND_ROWS()
/* Rápido */
SELECT * FROM tabla WHERE condición LIMIT 0, 10
SELECT COUNT(clave) FROM TABLE WHERE condición

Lo más lento es recuperar los registros y luego un script (PHP por ejemplo) contarlos.

PHP Mysql tips

Gracias diarioTHC por el enlace

Consejos para mejorar la paginación

ByLuis

La paginación es algo de lo más costoso a la hora de realizar una búsqueda. Si nos ponemos en el caso de miles de registros con ordenación por cierto campo, la búsqueda puede ser una query muy costosa. Si además unimos el cálculo total de registros encontrados, la página puede tardar mucho en devolverse.

Debido a ello, para agilizar este tipo de páginas existen unos consejos a tener en cuenta:

  • Cachea todo lo que se pueda: el caché evita que se realicen consultas a la BD.
  • No muestres todos los resultados: Google no lo hace y nadie se queja por ello. Limita el total de registros a 100 o 500, nadie visita más de 10 páginas normalmente.
  • No muestres el total de páginas o páginas intermedias: ofrece tan solo en enlace a “página siguiente”, para lo cual puedes obtener 21 registros y mostrar solo 20, así si la consulta te devuelve menos de 21 es que no hay página siguiente.
  • Estima el número de resultados: esto también lo hace Google, en vez de calcular el número exacto, se trata de estimar el número aproximado de resultados. Usa EXPLAIN para obtener ese número, puedes usar el campo rows como aproximación.

Four ways to optimize paginated displays