Too Cool for Internet Explorer

Sun compra MySQL 1

Hora y Fecha: Enero 17, 2008 @ 12:18 am Autor: Moisés Maciá
Categorías:
220 views

Iba a decir algo del Macbook Air no muy positivo, pero es que al enterarme de la compra de MySQL por parte de Sun Microsystems se me han quitado las ganas por completo. Un billón de dolares le ha costado a Sun la bromita, creo que ha sido la operación más cara en el mundo del software libre.

Con esta compra, Sun reafirma su apuesta a muerte por el software libre, obtiene una posición privilegiada en el mundo del desarrollo de aplicaciones y servicios web y pasa a competir con Oracle en el negocio de las bases de datos. Y aunque para que MySQL pueda mirar cara a cara a la todopoderosa Oracle le queda bastante camino por andar, ahí está.

En el último año MySQL cambió el rumbo acercándose más al mundo de los negocios con la versión Enterprise y todos los servicios de soporte asociados. También lanzaron un montón de software prometedor como el driver nativo para PHP mysqlnd y el dispatcher de conexiones MySQL proxy, entre otros.

Espero que Sun aporte programadores además de capital para agilizar el desarrollo del motor Falcon, mejorar el sistema de clustering, replicación, y como no, la integración con Java.



JOINs para seres humanos 22

Hora y Fecha: Abril 22, 2007 @ 10:50 pm Autor: Moisés Maciá
Categorías:
2,175 views

En este artículo voy a explicar cómo hacer uso del operador JOIN del álgebra relacional en el mundo real de las bases de datos y las ventajas que reporta en la selección de datos. No pongáis caras que no voy a hablar ni de álgebra relacional ni de teoría de conjuntos. Esto son JOINs para seres humanos :)

Qué es una JOIN

Es una operación que combina registros de dos tablas en una base de datos relacional que resulta en una nueva tabla (temporal) llamada tabla de JOIN. En el lenguaje de consulta SQL hay dos tipos de JOIN: INNER y OUTER, si bien cada vendedor añade a sus productos modificaciones y atajos para hacer más versátiles estas operaciones.

Como caso especial, una tabla (tabla base, vista o una tabla JOIN) puede realizar la operación JOIN sobre ella misma otra vez. Esto se conoce como self-JOIN.

Matemáticamente, un JOIN es una relación de composición. Estas son las operaciones fundamentales en el álgebra relacional.

Supongamos que tenemos dos tablas: una de películas y otra de directores relacionadas entre sí:

movies
id title year director
1 Four Rooms 1995 3
2 Die Hard 1988 1
3 The Hunt for Red October 1990 1
4 Psycho 1960 2
directors
id name
1 John McTiernan
2 Alfred Hitchcock
3 Quentin Tarantino

Si quiero sacar todas las peliculas y el nombre de su director haría lo siguiente:

  1. SELECT title, name
  2. FROM movies m, directors d
  3. WHERE m.director = d.id

Internamente la base de datos crearía una tabla temporal con todas las filas de la tabla movies cruzadas a su vez con todas las filas de la tabla directors, para después seleccionar las filas que cumplen la condición m.id = d.id. En total maneja 4×3 = 12 filas para obtener un resultado final de 4 registros.

En este caso no es problemático, pero cuando tenemos una tabla de un millón de registros y otra de diez millones, la cosa se vuelve muy fea: la base de datos tiene que manejar 10.000.000.000.000 filas cuando el resultado final quizá este formado por unas pocas decenas.

Aqui es donde entran en juego los JOINs: cuando aplicamos esa operación, la base de datos sólo devuelve el conjunto de filas afectadas por el JOIN, descartando todas las demás.

NOTA: esto no siempre es cierto, ya que las bases de datos modernas disponen de un optimizador de consultas que en determinados casos convertirá la sentencia SQL con el tradicional WHERE en una JOIN. Como he dicho en determinados casos funcionará bien, en otros no.

La sentencia reescrita como una JOIN quedaría de la siguiente manera:

  1. SELECT title, name
  2. FROM movies m
  3. INNER JOIN directors d ON (m.director = d.id);

INNER, OUTER, LEFT, … ¿Cuál utilizo en cada momento?

El circulo T1 representa todos los registros de nuestra primera tabla mientras que el circulo T2 representa todos los registros de la segunda tabla. Hay una intersección entre los dos circulos, eso representa los registros de ambas tablas que están relacionados entre sí por sus claves ajenas y primarias. ¿Fácil, no?

El color azul representará los datos que devuelve cada tipo de JOIN.

empty join

INNER JOIN

Una INNER JOIN sólo devuelve aquellos registros que coinciden en ambas tablas. Así cada registro que devuelva T1 debe tener su pareja en T2 enlazada por una clave ajena. En términos de lógica de primer orden sería equivalente al operador AND.

inner join

OUTER JOIN

Una OUTER JOIN es la operación complementaria a una INNER JOIN. Sólo devuelve aquellos registros que no estén emparejados en T1 y en T2. En términos de lógica de primer orden sería equivalente a la operación NOT AND.

outer join

LEFT JOIN

Una LEFT JOIN devuelve los registros que están en la tabla de la izquierda (T1) tanto si tienen pareja en T2 como si no.

Si tienen pareja, devuelve el dato relacionado. Si no, rellena los huecos con NULL.

left join

Es posible hacer la misma operación con la tabla de la derecha, en ese caso estaríamos hablando de una RIGHT JOIN pero lo habitual es utilizar como pivote siempre la izquierda.

LEFT OUTER JOIN

Una LEFT OUTER JOIN combina las ideas de la LEFT JOIN y la OUTER JOIN. Basicamente si utilizas una LEFT OUTER JOIN obendrás los registros de la tabla izquierda que no emparejan con ninguno de los de la tabla de la derecha.

left outer

De nuevo, se puede realizar la operación equivalente en la tabla de la derecha aunque no suele ser lo habitual.

θ JOIN

La composición Theta es el producto cartesiano de dos tablas. Existe como operación matemática pero normalmente no es una consulta que la gente utilice, porque devuelve todos los registros de todas las tablas.

theta join

Más sobre JOINs

Dependiendo del RDBMS que utilicéis, os será posible utilizar más tipos de composiciones (por ejemplo MySQL soporta las NATURAL JOIN y STRAIGHT JOIN) así como definir composiciones que afecten a mas de dos tablas.

Estas que he comentado están disponibles en la mayoría de las bases de datos modernas.



Bases de datos open source 5

Hora y Fecha: Febrero 17, 2007 @ 1:17 am Autor: Moisés Maciá
Categorías:
1,052 views

No solo de MySQL vive el programador, hoy daremos un repaso al amplio abanico que ofrece el mundo del sofware libre en el ámbito de las bases de datos.

No es mi intención encontrar la mejor de ellas, o realizar un análisis en profundidad de todas y cada una de sus características, solo comentaré la variedad de soluciones que hay disponibles ahí fuera. Supongo que mis lectores habituales ya conocen que es el software libre, y porque se debería utilizar. Si no es así, echa un vistazo a los recursos del final del artículo.

Entonces, ¿cuales son las bases de datos de código libre? La mayoría de los lectores habrán oído hablar de MySQL. Es por definición la base de datos de código libre más popular del mundo después de todo. También está PostgreSQL (la base de datos de código libre más avanzada del mundo), así como Firebird (la base de datos relacional del nuevo milenio). Sin embargo, todavía hay más. BerkeleyDB, si bien no es directamente comparable, merece una mención, mientras que Computer Associates ha lanzado recientemente Ingres, e IBM ha hecho lo mismo con Cloudscape (bajo el nombre de Derby).

Echemos un vistazo a cada uno de estos productos:

Berkeley DB

Al contrario que muchas otras listadas aquí, Berkeley DB no es un DBMS completo, lleno de herramientas gráficas de reportes y cosas así. Ni siquiera tienen una capa de proceso de consultas. En lugar de eso, es un motor relacional de almacenamiento empotrado muy ligero, pensado para aplicaciones que no requieren una instalación de un DBMS completo. Los usuarios de MySQL tienen la opción de utilizar el motor de datos de Berkeley DB (al principio era a única manera de garantizar capacidades transaccionales en MySQL).

Derby

Si Derby tuviera una definición, probablemente sería la base de datos empotrada en Java más famosa del mundo. Esto da una idea del nicho que cubre este producto. IBM donó la base de datos comercial Cloudscape con el nombre de Derby bajo una licencia libre a la Fundación Apache, en la que permanece bajo consideración como un proyecto incubado, poco después de que Computer Associates hiciera lo propio con Ingres.

Cloudscape es una base de datos empotrada orientada a Java, lo cual significa que el DBMS forma parte de la aplicación Java, tiene un consumo de memoria muy reducido y está diseñada para funcionar sin la necesidad de tener a un DBA dedicado a su mantenimiento. No está enfocada a ser una base de datos de calidad empresarial, su función es cubrir las necesidades de los desarrolladores de Java.

Firebird

Inprise (ahora Borland) fue una de las primeras compañías comerciales en lanzar una base de datos bajo los términos del software libre cuando liberaron a principio del 2000 su producto Interbase. Interbase 6 fue lanzado bajo una variante de la licencia Mozilla. Borland ha lanzado desde entonces las versiones 6.5, 7, 7.1 y 7.5 de su producto comercial, pero Firebird ha continuado desarrollándose en paralelo basándose en la versión del código fuente de Interbase 6.0.

Firebird tiene una comunidad de usuarios pequeña pero muy activa, y hasta hace poco era el factor olvidado en los debates MySQL vs PostgreSQL. Firebird ha tenido siempre muchas más características que MySQL, y también, al contrario que PostgreSQL, siempre ha funcionado sin problemas bajo plataformas Windows, Linux y otras variantes de Unix. Firebird por otra parte también ha sido un poco maltratada por Borland desde su lanzamiento, en el sentido que no lanzaron todos sus componentes, y los scripts de compilación ¡ni siquiera funcionaban!

Una falta importante es el motor de replicación (Firebird dispone de una funcionalidad llamada shadowing, que mantiene una copia idéntica de la base de datos en producción, pero no es exactamente un método de replicación). Hay en marcha proyectos, la mayoría de ellos comerciales, pero los de código libre no parecen muy maduros, o integrados en el código base de Firebird. El desarrollo de Firebird también es relativamente lento, con la versión 1.5 lanzada recientemente se han centrado en reescribir el código en C++ (desde C), y no se han añadidos nuevas características. Sin embargo, Firebird es un DBMS relativamente maduro, y está dirigido a reemplazar sistemas Oracle y SQL Server en aplicaciones comerciales.

Hay disponibles dos sabores: Classic y Super Server, la versión Super server gestiona mejor los recursos, y es la más indicada para entornos de gran volumen (parece que la mayoría del desarrollo se enfoca en este campo). También hay tres variantes de SQL, que varían en la conformidad con los estándares ANSI.

Firebird tiene una base de usuarios leales, es un buen producto para trabajar, y si consigue aumentar su velocidad de desarrollo, asi como mejorar su marketing, será un competidor muy fuerte.

Ingres

Ingres empezó como un proyecto de base de datos relacional en la Universidad de California, Berkeley (la misma universidad americana directa o indirectamente responsable de muchas de las contribuciones al mundo del software libre, incluyendo BSD Unix, Postgres, vi y Tcl). Se convirtió en uno de los proyectos mas influyentes en el mundo de las bases de datos. Con el código lanzado bajo una variante de la licencia BSD, todos los productos propietarios Sybase, SQL Server e Informix tienen sus raíces en Ingres, así como PostgreSQL.

En 1982, Michael Stonebraker, uno de los fundadores del proyecto, fundó Ingres Corporation, e intentó comercializar el producto. ASK y, posteriormente, Computer Associates compraron el producto, y continuaron desarrollándolo y haciendo negocio con él. Sin embargo, con el resto de bases de datos de código abierto haciendo serias incursiones en el mercado de las bases de datos comerciales, optaron por liberar todo el código en 2004.

Ingres es conocida por su fiabilidad y su escalabilidad, pero sufre de una mala reputación al retrasarse en incorporar características, parece que no ha habido mucho desarrollo desde que Computer Associates adquirió el proyecto en 1994. Podría decirse que, dejando a un lado a Oracle, esta implantada en más entornos empresariales que ninguna otra base de datos de código libre, y está por delante de MySQL en características, sin embargo la documentación disponible en linea es bastante pobre en comparación con las otras. Sus características mas importantes incluyen:

  • Consultas paralelas (permitiendo una escalabilidad soberbia, una simple consulta puede dividirse en componentes que se ejecutan a lo largo de todos los recursos disponibles).
  • Reorganización de tablas online (no es necesario parar la base de datos para realizar las rutinas de mantenimiento).

CA también subvenciona el proyecto a través del programa Million Dollar Challenge, por el que la comunidad de desarrolladores está invitada a crear soluciones que permitan migrar sistemas Oracle, SQL Server, Sybase, DB2, Informix, y/o MySQL a Ingres. Se anunciaron seis ganadores durante la conferencia anual de CA en Abril de 2005.

La liberación de Ingres bajo una licencia de software libre le ha dado una segunda oportunidad al proyecto, y se esperan ver grandes avances en el desarrollo del producto.

MySQL

MySQL es conocida por ser la base de datos más popular, impulsada por el boom de Internet, es utilizada ampliamente para aplicaciones web. En principio rápida y fácil de utilizar, pero con funcionalidad limitada, MySQL ha trabajado en dotar de nuevas funcionalidades al producto, y aunque todavía carece de presencia en el ámbito empresarial más exigente, su popularidad, y su gran base de usuarios significan que hay muchísimas herramientas y aplicaciones que trabajan con MySQL, con toneladas de soporte extra oficial que complementa el soporte oficial de MySQL.

MySQL no tiene demasiada potencia en vistas, triggers y procedimientos almacenados, características importantes para muchos usuarios.

MySQL alcanzó un hito importante cuando SAP licenció el uso de su tecnología SAPDB a MySQL. SAPDB es un DBMS maduro y lleno de funcionalidades que, aunque no esta integrado completamente con el resto de MSQL, dota al sistema de un entorno preparado para las empresas respondiendo de esta manera a los más críticos. MySQL ha cobrado una tremenda fuerza después de esto, y si son capaces de implementar rápidamente las características que faltan, mientras mantienen la facilidad y velocidad del producto, tendrán una combinación imbatible.

PostgreSQL

PostgreSQL no existía como tal hasta 1996, cuando Postgres95 cambió de nombre, reflejando la importancia del recientemente añadido interprete de SQL. Antes de esto Postgres, también un proyecto del famoso Michael Stonebraker y diseñado para ser el sucesor de Ingres, usaba QUEL, que es bastante parecido a SQL y discutiblemente más consistente en su estructura. Sin embargo, la predominancia de bases de datos basadas en SQL como Oracle y DB2 hacían que QUEL no obtuviera el favor de la industria y se dejara de lado.

Afortunadamente, Postgres fue lanzado bajo la licencia BSD, y aunque el proyecto auspiciado por la Universidad de Berkeley había terminado, el código seguía disponible libremente, el proyecto continuó en desarrollo hasta lo que conocemos hoy en día.

Hasta el reciente lanzamiento de la versión 8.0, PostgreSQL no estaba disponible para Windows, pero es de esperar que que su cuota de mercado se incremente en la medida que los usuarios de Windows empiecen a descubrirlo. Su fama de avanzada no es en vano. Dispone de casi todas las características requeridas para ser una base de datos de nivel empresarial, así como un buen número de características poco habituales y potencialmente útiles:

  • Tipos de datos y operadores definidos por el usuario.
  • Herencia de tablas (donde una tabla puede heredar todas las columnas de su padre, pero las demás pueden ser definidas)
  • Indices parciales o por expresión (indices definidos sólo en una parte de la tabla, limitados por una condición, o creados en la salida de una expresión)
  • Múltiples lenguajes para procedimientos almacenados (incluyendo el nativo PL/PgSQL, PL/PHP, PL/Perl y PL/Python)

PostgreSQL era lento y no proporcionaba binarios nativos para Windows. Hoy en día han rectificado estas cosas y la base de datos está experimentando una aceleración en su crecimiento.

Recursos



Paginar una consulta desde la consola MySQL 4

Hora y Fecha: Febrero 4, 2007 @ 2:49 am Autor: Moisés Maciá
Categorías:
686 views

Este truco va para los amantes de la línea de comandos y para los que han salido escaldados de las herramientas desktop de MySQL:

En muchas ocasiones, al realizar una consulta, la pantalla se llena de datos y el buffer del terminal acaba por agotarse, de forma que resulta imposible ver detenidamente todas las filas del resultado, especialmente las primeras.

Más de una vez he pensado que sería genial disponer de una herramienta como less para trabajar dentro de la consola de MySQL. Hasta que el otro día ví la luz:

mysql> \P less
PAGER set to 'less'

mysql> select foo,bar from table

Simplemente genial.



MySQL FULL TEXT para humanos 13

Hora y Fecha: Enero 14, 2007 @ 10:39 pm Autor: Moisés Maciá
Categorías:
1,736 views

En la base de datos MySQL existe una consulta que devuelve filas atendiendo a su relevancia. ¿Pero qué es relevancia? Es un número de coma flotante resultado de la aplicación de una serie de formulas. Conociendo la manera en la que estas formulas funcionan se pueden construir poderosas consultas que devuelvan resultados relevantes para los usuarios de nuestras aplicaciones.

En este artículo voy a comentar como se gestionan los índices FULLTEXT en la base de datos MySQL, versiones 4.1 o superior.

Primero crearemos una tabla de ejemplo:

  1.  
  2. CREATE TABLE quotes (quote CHAR(100),FULLTEXT (quote));
  3.  
  4. INSERT INTO quotes VALUES
  5.   (‘Special times require special socks’),
  6.   (‘Knock three times on the ceiling’),
  7.   (‘Boliauns are weeds’),
  8.   (‘The leprechaun’’s gold’);

Frases extraídas de The Field of Boliauns, un cuento clásico de la cultura Celta.

Algunas de las palabras se repiten varias veces. Otras palabras no formarán parte del indice FULLTEXT debido a que son muy cortas o demasiado frecuentes. Esta base de datos es lo suficientemente compleja para mostrar todos los trucos de las formulas de cálculo de relevancia.

myisam_ftdump

Para ver que es lo que se ha guardado en el indice FULLTEXT utilizaremos el programa myisam_ftdump. Viene con la distribución estándar de la base de datos.

Lo primerro es saber dónde se están guardando físicamente los datos de la tabla con una sentencia SHOW:

  1. SHOW VARIABLES LIKE ‘datadir%’;
+---------------+-----------------------+
| Variable_name | Value                 |
+---------------+-----------------------+
| datadir       | /var/lib/mysql/        |
+---------------+-----------------------+

1 row in set (0.00 sec)

Otra sentencia SELECT para saber el nombre de la base de datos en la que me encuentro:

  1. SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| db1        |
+------------+

1 row in set (0.01 sec)

Todos estos datos son los que necesito para poder ejecutar myisam_ftdump. Veamos primero las opciones que tiene:

$> myisam_ftdump --help

Use: myisam_ftdump <table_name> <index_num>

  -d, --dump          Dump index (incl. data offsets and word weights).
  -s, --stats         Report global stats.
  -v, --verbose       Be verbose.
  -c, --count         Calculate per-word stats (counts and global weights).
  -l, --length        Report length distribution.
  -h, --help          Display help and exit.
  -?, --help          Synonym for -h.

Veamos que aspecto tiene el volcado del indice:

$> myisam_ftdump /var/lib/mysql/db1/quotes 0 -d
       ca            0.9775171 boliauns
       65            0.9666505 ceiling
      12f            0.9775171 gold
       65            0.9666505 knock
      12f            0.9775171 leprechaun's
        0            0.8148246 require
        0            0.8148246 socks
        0            1.3796179 special
        0            0.8148246 times
       65            0.9666505 times
       ca            0.9775171 weeds

Veamos ahora los pesos por palabra:

$> myisam_ftdump /var/lib/mysql/db1/quotes 0 -c
        1            1.0986123 boliauns
        1            1.0986123 ceiling
        1            1.0986123 gold
        1            1.0986123 knock
        1            1.0986123 leprechaun's
        1            1.0986123 require
        1            1.0986123 socks
        1            1.0986123 special
        2            0.0000000 times
        1            1.0986123 weeds

Claramente se observa que MySQL asocia números a términos, lo que nos queda por ver es qué significan esos números.

Las formulas

Hay tres formulas, y no son para nada complicadas :)

  • peso_local = (log(dtf)+1)/sumdtf * U/(1+0.0115*U)
  • peso_global = log((N-nf)/nf)
  • peso_consulta = peso_local * peso_global * qf
Parámetros
  • dtf el número de veces que el término aparece en la fila.
  • sumdtf el sumatorio de (log(dtf)+1) para todos los términos de la misma fila.
  • U el número de términos únicos que hay en la fila.
  • N el número de filas que hay en la tabla.
  • nf el número de filas que contienen el término.
  • qf el número de veces que el término aparece en la consulta.

Por último log(n) hace referencia al logaritmo neperiano de n.

Tomemos por ejemplo la búsqueda de la palabra special sobre la primera fila de la tabla, que myisam_ftdump identifica como fila 0 (cero).

Para la primera formula: (log(dtf)+1)/sumdtf * U/(1+0.0115*U);

dft special aparece dos veces en la fila 0, así que log(dtf()+1) = 0.6931472 + 1 = 1.6931472
sumdft special aparece 2 veces en la fila 0, añadimos log(2)+1
times aparece 1 vez en la fila 0, añadimos log(1)+1
require aparece 1 vez en la fila 0, añadimos log(1)+1
socks aparece 1 vez en la fila 0, añadimos log(1)+1
el cálculo queda sumdtf = log(2)+1 + (log(1)+1)*3 = 4.6931472
U Hay 4 términos únicos en la fila 0, por lo que U/(1+0.115*U) = 4/(1+0.0115*4) = 3.824092

peso_local = 1.6931472 / 4.6931472 * 3.824092 = 1.3796179. El mismo número que sale en el volcado del indice proporcionado por myisam_ftdump.

Para la segunda formula: log((N-nf)/nf);

N Hay 4 filas en la tabla quotes
nf El término special tiene ocurrencias en 1 fila.

peso_global = log((N-nf)/nf) = log(3) = 1.0986123. El mismo número que devuelve myisam_ftdump en el volcado de pesos por palabra.

Para la tercera fórmula: peso_consulta = peso_local * peso_global * qf;

peso_local 1.3796179
peso_global 1.0986123
qf special aparece 1 vez en la consulta.

peso_consulta = 1.3796179 * 1.0986123 * 1 = 1.5156652. Finalmente esta es la relevancia de la consulta sobre la tabla de ejemplo.

Comprobemos el resultado con una consulta del tipo MATCHAGAINST:

  1.  
  2. SELECT ROUND(MATCH(quote) AGAINST (’special’),7) AS score FROM quotes;
+-------------------------------------------+
| score |
+-------------------------------------------+
|                                 1.5156652 |
|                                 0.0000000 |
|                                 0.0000000 |
|                                 0.0000000 |
+-------------------------------------------+

4 rows in set (0.00 sec)

NOTA: MySQL devuelve un número con mayor resolución que mi calculadora casio, así que me ha tocado redondear a 7 decimales para que salgan los cálculos :)

La consulta devuelve una puntuación de 1.5156652 para la primera columna de la tabla, el mismo peso que sale de nuestros cálculos.

Explicación de la fórmula

Observad que el peso local depende de una constante que lo multiplica. Esta constante es un factor de corrección para tratar una alta frecuencia de términos dentro de la fila. Para aclararnos: si un término aparece muchas veces en una fila, el peso crece.

¿Por qué el peso local depende de las veces que el termino aparece en la fila? Piensa en este mismo artículo, los términos MySQL y FULLTEXT aparecen muchas veces. Esto es típico: si una serie de palabras aparecen muchas veces a la fuerza deben ser relevantes.

Observad que el peso global depende de la multiplicación de una inversa, el número de filas menos el número de filas en la que aparece el término. Para aclararnos: si un término aparece en muchas filas, el peso baja.

El peso global baja con la frecuencia de un término en todas las filas de la tabla, esto es así porque se considera un término común. Si una palabra aparece constantemente en todas las filas se convierte en un patrón de búsqueda inútil. Pensad en buscar cosas como artículos, pronombres, etc. son tan frecuentes que los resultados son inservibles.

Observad que el peso local, el peso global y el peso de la consulta es lo único que importa. MySQL no incrementará el peso si dos términos aparecen cerca uno de otro. MySQL tampoco entiende las raíces semánticas, los tiempos verbales ni los plurales, esto es, si buscas weed no te va a hacer los cálculos de relevancia para weeds.

La búsqueda utilizando grafos de proximidad y raíces semánticas (stemming) son algunas de las novedades que están preparando de cara a la versión 5.2 de MySQL. Quizá no tardemos mucho en disfrutar de ellas.

MySQL tiene muchas opciones, incluyendo IN BOOLEAN MODE donde las formulas de arriba se vuelven irrelevantes. El modo booleano básicamente realiza una búsqueda en el histograma de frecuencias de la tabla.

La lista stopword

Algunos términos — los artículos, conjunciones y pronombres son un buen ejemplo — tendrán un peso global igual a cero. MySQL no los indexará ni los tendrá en cuenta en las consultas. Es lo que se conoce como stopwords. MySQL maneja una lista de stopwords por defecto para el idioma inglés, que puede ocasionar más problemas de los que resuelve.

Por ejemplo, supongamos el texto:

Every word she writes is a lie, including “and” and “the”.
It depends on what the meaning of the word “is” is.

Es imposible buscar los términos and and the o is is porque son stopwords. Para devolver resultados deberíamos utilizar el operador LIKE, por ejemplo:

  1.  
  2. SELECT * FROM quotes WHERE quote LIKE ‘%is" is%’;

O bien, utilizar una frase de busqueda que no incluya un stopword, por ejemplo:

SELECT * FROM quotes WHERE MATCH(quote) AGAINST('including and and the');

Otro inconveniente es que los stopwords por defecto pueden ser palabras válidas en otros idiomas distintos del inglés. Por ejemplo Is es el nombre de un río ruso y the es la palabra francesa para referirse al . Definitivamente parece que tenemos que cambiar la dichosa lista …

Hay listas de stopwords en la Universidad de Neuchatel provenientes del estudio de gramáticas y lenguajes del departamento de computación. Hay listas para los idiomas Finlandés, Francés, Alemán, Italiano, Ruso, Español y Sueco.

Para decirle a MySQL que utilice nuestra lista hay que levantar la base de datos con el siguiente parámetro:

#> mysqld --ft_stopword_file=stopword.txt

Podemos comprobar la lista que esta utilizando con una consulta SHOW:

  1. SHOW VARIABLES LIKE ‘ft_stopword_file’;
+------------------+--------------+
| Variable_name    | Value        |
+------------------+--------------+
| ft_stopword_file | stopword.txt |
+------------------+--------------+

1 row in set (0.00 sec)

Algunos trucos

Debido a que la formula final depende del parámetro qf (la frecuencia de un término en la consulta), se puede otorgar un peso extra a un término tan sólo con ponerlo varias veces dentro de la consulta. Si preguntamos a la base de datos por special special en lugar de por special veremos como la relevancia cambia.

Suele ayudar bastante añadir una columna con los términos que uno considere relevantes y asignarle un indice FULLTEXT. Es una idea similar a la folcsonomía (ordenación por etiquetas o tags) que tan de moda está en estos días. Los keywords seleccionados otorgarán un mayor peso a la fila.

En ocasiones las consultas devuelven resultados muy extraños, ejecuta myisam_ftdump. Quizá no te salvará el culo pero los resultados te pueden ayudar a mejorar las estrategias de búsqueda la próxima vez.

Actualmente InnoDB no tiene soporte para los indices FULLEXT: deberás elegir entre tablas con indice FULLEXT o tablas con integridad referencial. Al menos hasta la próxima versión 5.2, dónde está previsto que todos los motores de datos de MySQL respeten la integridad referencial.

Los lenguajes ideográficos como el Chino, el Japonés y el Indú carecen de delimitadores entre palabras, escriben todo seguido sin espacios. Esto representa un impedimento enorme en el estudio de todo lo que se ha comentado en el articulo, por esa razón no hay soporte FULLEXT en estos idiomas.

Existe un motor de datos para MySQL que incorpora muchas más funcionalidades en el campo de las búsquedas FULLEXT, entre ellas el soporte para lenguajes ideográficos, grafos de proximidad y stemming. Actualmente está en intensivo desarrollo y no hay paquetes para instalarlo fácilmente: Sphinx.

Espero que este artículo os ayude a mejorar la precisión de vuestras búsquedas y se traduzca en mejores aplicaciones para los usuarios.



Multiple ‘GROUP BY’ en SQL 2

Hora y Fecha: Noviembre 30, 2006 @ 12:45 am Autor: Moisés Maciá
Categorías:
1,098 views

Os planteo una dudilla típica en el mundo de las bases de datos: tenemos una tabla con todos los empleados de una empresa, cada empleado tiene un único perfil asociado y además cada uno de ellos puede tener (o no) una serie de atributos.

Lo que quiero es obtener una tabla de frecuencias en la que diga cuantos empleados pertenecientes a un perfil y con una serie de atributos, empiezan con la letra A, con la B, con la C y así hasta la Z.

Es decir agrupar los empleados por perfil/atributo y después por letra alfabetica; algo parecido a una nube de tags o un histográma.

La instrucción GROUP BY de SQL sólo nos permite agrupar por un campo, entonces ¿Se puede sacar con una sola consulta sin utilizar programación ni tablas temporales? Si.

Veamos el esquema de datos sobre MySQL:

Tabla employees, almacena los datos relativos a los empleados:

  1.  
  2. CREATE TABLE employees (
  3.   id int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  4.   name varchar(50),
  5.   profile_id int(10) UNSIGNED NOT NULL,
  6.   PRIMARY KEY (id)
  7. ) ENGINE=MyISAM;

Tabla profile, almacena el perfil de los empleados (uno por empleado):

  1.  
  2. CREATE TABLE profiles (
  3.   id int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  4.   name varchar(50),
  5.   PRIMARY KEY (id)
  6. ) ENGINE=MyISAM;

Tabla attributes, almacena todos los atributos:

  1.  
  2. CREATE TABLE attributes (
  3.   id int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  4.   name varchar(50),
  5.   PRIMARY KEY (id)
  6. ) ENGINE=MyISAM;

Tabla employee_attributes, almacena las relaciones entre empleados y atributos:

  1.  
  2. CREATE TABLE employee_attributes (
  3.   id int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  4.   employee_id int(10) UNSIGNED NOT NULL,
  5.   attribute_id int(10) UNSIGNED NOT NULL,
  6.   PRIMARY KEY (id)
  7. ) ENGINE=MyISAM;

Insertamos algunos datos de prueba:

  1.  
  2. INSERT INTO profiles (name) VALUES(‘perfil de programador’);
  3. INSERT INTO profiles (name) VALUES(‘perfil de manager’);
  4. INSERT INTO profiles (name) VALUES(‘perfil de tester’);
  5.  
  6. INSERT INTO attributes (name) VALUES(‘jornada completa’);
  7. INSERT INTO attributes (name) VALUES(‘empleado del mes’);
  8. INSERT INTO attributes (name) VALUES(‘encargado de sección’);
  9. INSERT INTO attributes (name) VALUES(‘le toca guardia’);
  10.  
  11. INSERT INTO employees (name,profile_id) VALUES(‘pepito’, 1);
  12. INSERT INTO employees (name,profile_id) VALUES(‘juanito’, 1);
  13. INSERT INTO employees (name,profile_id) VALUES(‘manolito’, 1);
  14. INSERT INTO employees (name,profile_id) VALUES(‘menganito’, 1);
  15.  
  16. INSERT INTO employee_attributes (employee_id,attribute_id) VALUES(1,1);
  17. INSERT INTO employee_attributes (employee_id,attribute_id) VALUES(1,3);
  18. INSERT INTO employee_attributes (employee_id,attribute_id) VALUES(2,1);
  19. INSERT INTO employee_attributes (employee_id,attribute_id) VALUES(2,2);
  20. INSERT INTO employee_attributes (employee_id,attribute_id) VALUES(2,3);
  21. INSERT INTO employee_attributes (employee_id,attribute_id) VALUES(3,1);
  22. INSERT INTO employee_attributes (employee_id,attribute_id) VALUES(3,4);
  23. INSERT INTO employee_attributes (employee_id,attribute_id) VALUES(3,3);
  24. INSERT INTO employee_attributes (employee_id,attribute_id) VALUES(3,2);

La solución Quick&Dirt empleada masivamente en estos casos es hacer una consulta para filtrar los empleados que nos interesan mediante la siguiente consulta:

  1.  
  2. SELECT Employee.id, Employee.name
  3. FROM employees AS Employee, employee_attributes AS EmployeeAttr
  4. WHERE Employee.id = EmployeeAttr.employee_id
  5.     AND EmployeeAttr.attribute_id IN (1,2,3)
  6.     AND Employee.profile_id = 1
  7. GROUP BY Employee.id

Resultado:

  id    name
----------------
  1    pepito
  2    juanito
  3    manolito
  4    menganito

… para después mediante programación, agruparlos y realizar el conteo.

¿Es óptimo? NO, hay que hacer un tratamiento a posteriori de los datos.

¿Es sencillo? NO, quizá sea más complicado y engorroso la programación del tratamiento posterior que hacerlo corréctamente.

¿Es elegante? NO, se mezcla la capa de negocio con la capa de datos.

¿Entonces cómo lo hago?

La mejor forma de hacer este tipo de consulta y mantener tu karma como programador es utilizar una Subquery. Una subquery es una manera especial de anidar sentencias SQL para componer consultas mas complejas que de otra forma no se podrían realizar.

Las subqueries se agrupan fundamentalmente en dos grandes grupos: escalares y correlacionadas, aunque la que voy a utilizar yo es un poco diferente puesto que el resultado de la subquery suplantará a una tabla; jugando con este efecto conseguimos el comportamiento de un GROUP BY multiple.

  • Más información en el apartado Subquery Syntax de la documentación de MySQL.
  • Más infromación sobre Subqueries correlacionadas y escalares (con muchos ejemplos) en MySQL Hispano.

Veamos la consulta:

  1.  
  2. SELECT LEFT(Employee.name,1) AS alphacrum, COUNT(LEFT(Employee.name,1)) AS total
  3. FROM (
  4.   SELECT Employee.id, Employee.name
  5.     FROM employees AS Employee, employee_attributes AS EmployeeAttr
  6.     WHERE Employee.id = EmployeeAttr.employee_id
  7.       AND EmployeeAttr.attribute_id IN (1,2,3)
  8.       AND Employee.profile_id = 1
  9.     GROUP BY Employee.id
  10.   ) AS Employee
  11. GROUP BY alphacrum
  12. ORDER BY alphacrum ASC;

Resultado:

  alphacrum    total
----------------------------
  j                    1
  m                  2
  p                   1

Como podeis observar, la tabla employees de la consulta principal no es la original; es el resultado del filtrado previo según los parametros que nos interesen.

Una vez filtrados es muy sencillo reordenar los datos a nuestro gusto, únicamente agrupamos por letra y realizamos el conteo de los resultados con COUNT, devolviendonos los datos tal y como nos hacen falta.

De esta manera se utiliza efectivamente el cache del servidor de bases de datos y la programación de la aplicación no se satura con un montón funciones de tratamiento de datos. Simple y elegante.



Cosas a tener en cuenta cuando diseñamos un modelo de datos 1

Hora y Fecha: Noviembre 22, 2006 @ 12:11 am Autor: Moisés Maciá
Categorías:
725 views

… y que la mayoría de los “programadores” ni siquiera saben que existen:

  1. Crea indices para las columnas, pero hazlo con cabeza. Muchos indices pueden hacer que las inserciones, borrados y actualizaciones sean operaciones muy lentas.
  2. Utiliza EXPLAIN para optimizar las consultas y observar como afectan los indices al rendimiento.
  3. Minimiza los datos que devuelven las consultas. No utilices SELECT *, trocea los datos en páginas con LIMIT e intenta agrupar varias consultas en una sola con UNION o JOIN.
  4. El mayor cuello de botella de las bases de datos suele ser el disco duro. Si necesitas rendimiento considera montar un RAID para datos y otro para los logs, así como dedicar una máquina únicamente para la BD. Manejar tablas temporales en memoria también mejora notablemente el rendimiento.
  5. LOAD DATA funciona mucho más rápido que INSERT: por cada inserción se actualiza el árbol de indices, así que si no te queda mas remedio que hacer muchos INSERT seguidos, desactiva los indices y actualizalos al terminar.
  6. No selecciones columnas de tipo BLOB o TEXT directamente, hazlo siempre a través de una tabla relacionada. Cuando se borran filas con estos tipos de datos, las tablas tienden a fragmentarse. Analiza y reconstruye los indices al eliminar filas de este tipo.
  7. Normaliza tu modelo, por lo menos hasta la tercera forma normal. En ocasiones se degradan las formas normales de alguna relación para ganar rendimiento, pero eso sólo es cierto en determinado tipo de situaciones muy específicas. Normaliza siempre.
  8. Considera utilizar el tipo de datos ENUM en lugar de una tabla relacionada.
  9. La integridad referencial y abusar de los triggers baja el rendimiento de las consultas. Activalos cuando los necesites pero no abuses.
  10. Si puedes hacerlo con un procedimiento almacenado, hazlo.
  11. Prepara benchmarks y mide el rendimiento de tus consultas sobre datos reales.

Si todo esto te queda grande, contrata a un DBA.



OpenOffice y SQLite 3

Hora y Fecha: Noviembre 14, 2006 @ 12:30 am Autor: Moisés Maciá
Categorías:
880 views

Las entradas sobre SQLite están teniendo mucho éxito entre los lectores de QuarkBlog. Algunos de vosotros me habéis preguntado si utilizo algún interfaz gráfico para borrar, insertar, actualizar filas y manejar las tablas y las consultas.

La verdad es que no, utilizo simplemente el interprete de comandos que viene de serie con la instalación de SQLite, escribiendo directamente las consultas en SQL. Realmente es lo más rápido y lo más potente, pero viendo que varias personas me lo han preguntado, he investigado un poco acerca del tema.

He encontrado muchos interfaces gráficos (sobre todo para Windows), todos ellos de pago o en su defecto shareware y además terriblemente malos.

Muchas veces nos empeñamos en buscar herramientas específicas sin pararnos a pensar que ya hay algo que soluciona el problema: OpenOffice :)

El resultado de la conjunción SQLite+OpenOffice es un gestor de bases de datos con todas las ventajas de Microsoft Access y ninguno de sus problemas.

Básicamente hay dos formas de conectar OpenOffice con SQLite: una me ha funcionado y la otra no. Rebuscando por la web de OpenOffice he dado con esta página en la que explican como instalar un driver específico. Este es el método que no me ha funcionado, aunque tampoco he insistido mucho …

La otra forma sólo he podido reproducirla en Windows, consiste en registrar un driver ODBC y crear una conexión de datos que podremos importar desde OpenOffice sin problema. Técnicamente esta solución también es válida para Linux, pero configurar fuentes ODBC es un dolor y no tengo tiempo.

El driver ODBC para SQLite lo podeis conseguir aquí (licencia BSD).

Aquí podéis ver una consulta:

SQLite sobre OpenOffice



Access Vs SQLite, los resultados 5

Hora y Fecha: Octubre 11, 2006 @ 12:08 am Autor: Moisés Maciá
Categorías:
1,290 views

Bien, bien, he esperado un poco para tener datos objetivos sobre el rendimiento de ambos motores de datos sobre una misma aplicación.

Estos datos, ni tienen rigor ni son extrapolables a otros casos, simplemente comento como fue el cambio de una tecnología a otra.

Tamaño de la base de datos

En ambos casos se almacenan las mismas tablas y los mismos datos: 5 tablas y alrededor de 2000 registros en total.

Microsoft Access: ~700Kb.
SQLite: ~170Kb.

Rendimiento en selecciones

SQLite se come con patatas al Access en este apartado, es apróximadamente 3 veces más rápido seleccionando datos que su rival (para mis tablas y datos, repito).

Rendimiendo en modificaciones/inserciones

SQLite pincha y gana por goleada Microsoft Access que es notablemente más rápido, alrededor de 4 veces más.
Esto se debe a todos los triggers que le puse para asegurar la integridad referencial, Access lo hace solito. Si le quitamos los triggers SQLite sigue estando un poco por encima de Access.

Facilidad de programación

Ambos son compatibles con ADO.Net que era la tecnología que tenía que emplear en este caso, asi que empate técnico en este apartado.
También hay que decir que la gente acostumbrada al estándar SQL se sentirá mucho más cómoda programando con SQLite.

Facilidad de mantenimiento

Atendiendo a la experiencia previa, Microsoft Access requiere de un mantenimiento y reparación periodica de las tablas para asegurar la integridad de los datos.

Me he encontrado en la desagradable situación de ver como aparecen y desaparecen registros de la base de datos como por arte de magia, y no era cosa de mi programa. Desde el propio gestor del Access ocurría lo mismo.

SQLite no necesita ningún tipo de mantenimiento, simplemente funciona.



SQLite y las claves ajenas 4

Hora y Fecha: Septiembre 25, 2006 @ 7:02 pm Autor: Moisés Maciá
Categorías:
1,003 views

Ya he dado carpetazo a Microsoft Access, estoy probando con SQLite (como debería haber hecho desde un principio) y de momento funciona perfecto con ADO.Net. Como ya tenía encapsulado el acceso a datos solamente he tenido que cambiar un par de lineas del programa para que funcionara con la nueva base de datos, más toda la morralla de sintaxis SQL de Access, obviamente …

Peeeero no todo son florecillas y mariposas, algún gnuero no identificado me dijo que SQLite implementaba integridad referencial mediante claves ajenas y toda la pesca. Pues no, tal y como dice la documentación acepta la sintaxis pero hace caso omiso de las referencias.

El caso es que me hacían mucha falta porque paso olímpicamente de comprobar a mano todas las tablas cada vez que actualizo, borro o inserto registros, ya he sufrido suficiente con MySQL este tema como para volver otra vez.

Vale, no tendrá integridad referencial pero con la dosis justa de cafeína y un rato peleando con los triggers he conseguido un comportamiento idéntico. MMWAHAHAHA!!

Veamos, pondré un ejemplo con dos tablas: empleados y departamentos; creo que la relación es obvia así que no comento nada más al respecto y paso a escribir la sentencia para crear las tablas en SQLite.

  1. CREATE TABLE departments(
  2.   id INTEGER PRIMARY KEY AUTOINCREMENT,
  3.   name VARCHAR(50)
  4. );  
  5.  
  6. CREATE TABLE employees(
  7.   id INTEGER PRIMARY KEY AUTOINCREMENT,
  8.   name VARCHAR(50),
  9.   department_id INTEGER NOT NULL
  10.     CONSTRAINT fk_department_id REFERENCES departments(‘id’) ON DELETE CASCADE );

En la declaración aparece una relación entra la tabla empleados y la tabla departamentos a través de su clave primaria, pero SQLite lo ignora por completo. Para poder asegurar la integridad de dicha relación obligatoriamente debemos utilizar una serie de triggers. Los triggers son una característica añadida a partir de la versión 2.5 de SQLite, actualmente andan por la versión 3.3.x asi que no debería haber problema si lo empleáis en sistemas modernos.

Cada restricción de clave debe tener tres triggers (tres tristes triggers … oh wait!): uno para las sentencias INSERT, otro para UPDATE y otro para DELETE.

Trigger para INSERT (ojo, permite valores nulos):

  1. CREATE TRIGGER fki_employees_department_id
  2. BEFORE INSERT ON employees
  3. FOR EACH ROW BEGIN
  4.   SELECT CASE
  5.     WHEN ((SELECT id FROM departments WHERE id = NEW.department_id) IS NULL)
  6.       THEN RAISE (ABORT,‘violacion de la restricción de integridad  "fk_department_id" por una sentencia INSERT’)
  7.   END;
  8. END;

En caso de que no queramos valores nulos en el campo, cambiamos ligeramente el código añadiendo una nueva condición:

  1. CREATE TRIGGER fki_employees_department_id
  2. BEFORE INSERT ON employees
  3. FOR EACH ROW BEGIN
  4.   SELECT CASE
  5.     WHEN ((NEW.department_id IS NOT NULL) AND (SELECT id FROM departments WHERE id = NEW.department_id) IS NULL)
  6.       THEN RAISE (ABORT,‘violacion de la restricción de integridad  "fk_department_id" por una sentencia INSERT’)
  7.   END;
  8. END;

Exactamente lo mismo para las sentencias UPDATE:

  1. CREATE TRIGGER fku_employees_department_id
  2. BEFORE UPDATE ON employees
  3. FOR EACH ROW BEGIN
  4.   SELECT CASE
  5.     WHEN ((SELECT id FROM departments WHERE id = NEW.department_id) IS NULL)
  6.       THEN RAISE (ABORT,‘violacion de la restricción de integridad  "fk_department_id" por una sentencia INSERT’)
  7.   END;
  8. END;

Para las sentencias DELETE, cambiamos el orden y aplicamos el trigger a la tabla departamentos:

  1. CREATE TRIGGER fkd_employees_department_id
  2. BEFORE DELETE ON departments
  3. FOR EACH ROW BEGIN
  4.   SELECT CASE
  5.     WHEN ((SELECT department_id FROM employees WHERE department_id = OLD.id) IS NOT NULL)
  6.     THEN RAISE(ABORT, ‘violación de la restricción de integridad "fk_department_id" por una sentencia DELETE)
  7.   END;
  8. END;

Este trigger previene el borrado de un departamento que contenga algún empleado, este suele ser el comportamiento habitual.

En ocasiones nos conviene eliminar todos los empleados en cascada cuando se elimina un departamento. El trigger que consigue este efecto quedaría de la siguiente manera:

  1. CREATE TRIGGER fkd_employees_department_id
  2. BEFORE DELETE ON departments
  3. FOR EACH ROW BEGIN
  4.   DELETE FROM employees WHERE department_id = OLD.id;
  5. END;

A que mola? :)




Entradas siguientes »

Bad Behavior has blocked 483 access attempts in the last 7 days.