Como acelerar las consultas en MySQL

Aunque MySQL lleva su propio sistema de optimización cuando lanzamos un "Query SQL", nos es posible mediante programación elegir el índice que utilizará el MySQL en cada una de las tablas para optimizar nuestra consulta.

Como es mejor un ejemplo que mil palabras, vamos a ilustrar un pequeño ejemplo desde nuestro querido Fox.

Creamos en nuestra BBDD de MySQL una pequeña tabla de apuntes contables. Con varios índices (No.Apunte,SubCuenta,Fecha etc).

DROP TABLE IF EXISTS apuntes;

CREATE TABLE apuntes (
  apunte decimal(8,0) default '0.00',
  fecha date default '2009-01-01',
  texto varchar(40) collate utf8_spanish_ci default '',
  cuenta varchar(10) collate utf8_spanish_ci default '0000000000',
  debe decimal(12,2) default '0.00',
  haber decimal(12,2) default '0.00',
  PRIMARY key  (apunte),
  key K01 (fecha),
  key K02 (cscta))
ENGINE = InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci ROW_FORMAT=DYNAMIC ;

Una vez visto el ejemplo de tabla, vamos a crear nuestra consulta en nuestro querido Fox. Y dependiendo de los datos que queramos extraer de la tabla, elegiremos que la consulta sea optimizada por un índice determinado.

*******************************************************************
*  Ejemplo de Consulta Optimizada para MySQL
*******************************************************************

KNDX = 'K01'
DO FORM FCONSULTA    && (FORMULARIO DE CAPTURA DE VARIABLES)
IF T1APTS = T2APTS
  KNDX = 'PRIMARY'
ENDIF

IF T1FECHA = T2FECHA
  KNDX = 'K01'
ENDIF

IF T1CUENTA = T2CUENTA
  KNDX = 'K02'
ENDIF

TEXT TO XSQL TEXTMERGE NOSHOW
  SELECT  *
    FROM  apuntes FORCE INDEX(<<KNDX>>)
    WHERE apunte >= ?T1APTS AND apunte <= ?T2APTS AND
    fecha >= ?T1FECHA AND fecha <= ?T2FECHA AND
    cuenta >= ?T1CUENTA AND cuenta <= ?T2CUENTA
    ORDER BY apunte,fecha 
ENDTEXT

SQLPREPARE(NH, '' + XSQL, 'TCURSOR')

DO WHILE SQLEXEC(NH) = 0
ENDDO

BROWSE

*******************************************************************

Espero que el código anterior sea descriptivo de lo que os quiero transmitir.
En el ejemplo anterior utilizamos la variable KNDX que es la que almacena el índice que queremos utilizar para optimizar nuestra consulta. Por defecto en este ejemplo concreto ponemos el valor del indice fecha 'K01'. Y utilizamos la orden FORCE INDEX para forzar al MySQL que utilice el índice que le indicamos en cada consulta dependiendo de los valores que queramos extraer en cada momento.

En tablas relativamente pequeñas es posible que no se aprecie rendimiento alguno. Pero en consulta a tablas con mas de 2 Millones de registros, como es mi caso. Os aseguro que el rendimiento es exponencial.

Un cordial saludo a la comunidad Fox.

Antonio L. Montagut
www.ontarioxb.es