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

Similar Posts

Optimizaciones para MySQL

ByLuis

Ya el otro día hablábamos sobre las optimizaciones que podríamos realizar en nuestro entorno LAMP referente a Apache y PHP. Ahora, la gente de IBM nos vuelve a ofrecer otro artículo en el que nos indican cosas que hacer para mejorar el rendimiento de nuestra base de datos MySQL.
Las mejoras que se pueden realizar se dividen en tres conceptos: optimizar la máquina, configurar el proceso de MySQL y optimizar las querys. El servidor de base de datos nos puede dar problemas ya que una base de datos consume muchos recursos, lo cual se puede solucionar en muchos casos mejorando la CPU y la velocidad de disco duro, y si no, aumentar la RAM, cosas lógicas y normales.
Para la optimización de MySQL y el tratamiento de las querys lentas, se pueden realizar varias acciones. La primera de ella sería tener un log de qué querys emplean mucho tiempo en ejecutarse, para lo cual deberemos cambiar en la configuración de MySQL el parámetro log-slow-queries para que se guarden qué querys tardan más de cierto tiempo.
Otro tema importante es cachear las querys que se realizan, para ello hay dos formas, la que nos cuentan en el artículo, que es mediante configuración, y la otra que es mediante código, porque si lo realizamos mediante código podemos controlar mejor que querys cacheamos.
Al igual que ocurre en Apache, en MySQL es importante controlar el número máximo de conexiones, el timeout y el número máximo de errores, ya que si configuramos mal estos valores nos podemos encontrar con rendimientos poco deseados.
En total, una serie de recomendaciones muy buenas que debemos tener en cuenta cuando nuestra aplicación web requiere ajustar los recursos al máximo.
Tuning LAMP systems, Part 3: Tuning your MySQL server

GreenSQL: proxy que protege MySQL de SQL injections

ByLuis

GreenSQL es un reverse proxy que se sitúa entre tu aplicación web y la BD de MySQL y evita que se realicen ataques de SQL injection en nuestra BD.
greensql.png
La lógica está basada en la evaluación de los comandos SQL usando una matriz de puntuación de riesgo, a parte de bloquear comandos de administración (DROP, CREATE, …).
Tiene muy buena pinta, claro que habría que hacer unas pruebas de rendimiento para ver si es más eficiente evitar los ataques mediante código o mediante GreenSQL.
GreenSQL

myisamchk: comprobar y reparar tablas MYISAM en MySQL

ByLuis

Generalmente las tablas se vuelven corruptas cuando el servidor tiene un problema crítico y finaliza el servicio o cuando las tablas no se cierran correctamente. Siendo los ficheros de índices (.MYI) y los de datos (.MYD) los que están corruptos y los que hacen que no se sincronicen correctamente.

Normalmente se puede comprobar el estado y reparar una tabla mediante dos simples instrucciones MySQL:

CHECK TABLE tabla;
REPAIR TABLE tabla;

Pero esto no es siempre efectivo, conociéndose casos en el que no lo repara del todo o que dice que la reparación ha sido un éxito pero realmente no ha sido así. Por ello el mejor método para reparar una tabla corrupta es usar el comando de MySQL myisamchk, recomendando siempre parar el servidor corrupto para realizar la reparación.

myisamchk [opciones] ./data//.MYI

Aunque la manera más rápida de comprobar las tablas es con las opciones –silent y –fast, la mejor opción para una recuperación completa es –safe-recover, aunque lógicamente toma mucho tiempo.

Vía / Whatever….

|

Comentarios anidados con PHP y MySQL

ByLuis

Los comentarios anidados es algo muy común entre los foros o blogs. Realizarlo es bastante sencillo, aunque quizás hacerlo de la forma más eficiente puede tener su dificultad.
En el post que referencio, nos explican paso a paso cómo hacerlo: estructura de la BD, formulario, consultas, …
La solución es de las sencillas, se trata de que cada comentario tenga un campo que enlace con el comentario padre, y luego a la hora de recuperar los comentarios, hay que ir realizando consultas de obtención de comentarios por cada comentario padre, salvo en el primer caso que se buscan los comentarios iniciales.
HOW-TO: Multi-level Comments In PHP

Recuperar datos en InnoDB

ByLuis

Data Recovery Toolkit for InnoDB es un conjunto de herramientas que comprueba los tablespaces de InnoDB y recuperan datos de un tablespaces dañado o de tablas que han sufrido drop o truncate.

Entre las características que ofrece nos encontramos con:

  • Soporte para REDUNDANT (anterior a MySQL 5.0) y COMPACT (posterior a MySQL 5.0).
  • Funciona con un tablespace único o un tablespaces de un fichero por tabla.
  • Recupera datos incluso cuando una página InnoDB has sido reasignada a otra tabla o fue parcialmente destruida.
  • Permite todos los tipos de datos de MySQL excepto BLOBs, SETs y BITs, aunque está previsto para la próxima versión.
  • Tiene un conjunto de filtros de datos para definir rangos de datos (para números), longitud de campos (para campos de longitud variable), juegos de caracteres (para strings), periodos de datos, etc…
  • Viene acompañado de una herramienta que permite definir tablas InnoDB, así no es necesario implementarlas directamente mediante sentencias SQL.
  • Los datos se devuelven en formato CSV por lo que luego pueden ser importados facilmente.

Data Recovery Toolkit for InnoDB

Vía / Homo-Adminus Blog

Intercambiando el valor de columnas en MYSQL

ByLuis

Interesantes métodos para intercambiar el valor de dos columnas en MySQL.

UPDATE swap_test SET x=y, y=@temp WHERE (@temp:=x) IS NOT NULL;

Este método no funciona cuando alguna de las columnas tiene valor NULL, y los paréntesis son obligatorios.

Otro método, que no tendría problemas con los NULL, sería:

UPDATE swaptest s1, swaptest s2 SET s1.x=s1.y, s1.y=s2.x WHERE s1.id=s2.id;

Swapping Column Values in MySQL