MYSQL e indices

[J. Ernesto]

Foro buenas

Usando la clase tdolphin ya pude hacer
1- Crear las tablas para la base de datos,
2- Usando dbftosql ( de keyler ), trasladar tablas de dbf a sql (archivos con mas de 5 millones de registros),
3- conectar varios pcs al servidor, (con windows 5_xp, 6_vista, 7 y 8.1)
4- hacer querys sencillos.

1- Ahora cómo averiguar vía fivewin y tdolphin los nombres de los archivos indices de las tablas de la BBDD, para borrarlos y volverlos a generar?
2- Al seleccionar una tabla como cambiar de indice para buscar datos, similar a setorder()

Gracias.

[carlos vargas]

en sql no existe tal cosa como seleccionar un indice.
solo es necesario que nosotros determinemos que indices crear para una tabla segun nuestros analisis, una vez hecho esto, nos olvidamos de los indices.
es el propio motor quien se encarga de seleccionar los indices necesarios para buscar la información de un select o un update.
a esto se le llama plan.

por ejemplo:

Code: Select all  Expand view  RUN

select num_clie, nombre, cedula from clientes where num_clie=100

aca vemos que la clave de busqueda es la columna "num_clie", entonces es necesario que nosotros creemos un indices en la tabla cliente por la columna "num_clien".
si el indice existe, al motor sql le tomara 1 fila encontrar la información solicitada del cliente numero 100, pero si no hay indice por esa columna, entonces el motor necesitara recorrer la totalidad de las filas de la tabla clientes para retornar una sola fila.

aca algo que señalar es que en este caso en necesario que el indice sea "unique" por que el numero de un cliente debe ser unico.
y si queremos optimizar un poco mas la cosa se podría usar:

Code: Select all  Expand view  RUN

select num_clie, nombre, cedula from clientes where num_clie=100 limit 1

con "limit 1" le informamos tajante mente al motor que nuestro resultado es de una sola fila, por lo que no es necesario que busque mas.

por norma general, toda columna involucrada en un where, order by o group by debe tener un indice

un dato importante es que en mysql tenemos una herramienta muy importante "describe":
si nosotros indicamos:

Code: Select all  Expand view  RUN

describe select num_clie, nombre, cédula from clientes where num_clie=100 limit 1

aca pones "describe" antes de una consulta, entonces ocurre que el select en cuestión no retornara las filas solicitadas, si no que el select retornara la info de como el motor sql busco la información que le solicitamos.

no informa, que tabla uso, la llave de indice que utilizo para la búsqueda, asi como cuantas filas tubo que procesar para encontrar la informacion solicitada.



todos, esos tips lo he encontrado en este blog, que si bien trata de firebirdsql, la teoría es aplicable a cualquier motor sql, ademas que walter se las pinta para encontrar y explicar información.
http://firebird21.wordpress.com/

se las recomiendo.

he aprendido un montón de trucos y optimizaciones.

[carlos vargas]

En cuanto a la creación y eliminación de indices, siguiendo los ejemplos anteriores:
tdolphin, tiene métodos para esto:
Para creación de indices:

Code: Select all  Expand view  RUN

METHOD CreateIndex( cName, cTable, aFNames, nCons, nType )

Para eliminar indices:

Code: Select all  Expand view  RUN

METHOD DeleteIndex( cName, cTable )

pero, en lo personal yo uso las ordenes directas:

Code: Select all  Expand view  RUN

oServer:Execute( "CREATE INDEX UNIQUE CLIENTES_NUM_CLIE ON CLIENTES(NUM_CLIE)" )
oServer:Execute( "CREATE INDEX             CLIENTES_NOMBRE    ON CLIENTES(NOMBRE  )" )
oServer:Execute( "CREATE INDEX             CLIENTES_CEDULA     ON CLIENTES(CEDULA  )" )
 

para borrar, creo que es:

oServer:Execute( "DROP INDEX CLIENTES_NUM_CLIE" )
oServer:Execute( "DROP INDEX CLIENTES_NOMBRE" )
oServer:Execute( "DROP INDEX CLIENTES_CEDULA" )

acá yo defino el nombre del indice iniciando con el nombre de la tabla gion bajo mas nombre de columna.

adicionalmente yo uso una columna adicional en cada tabla la cual llamo MY_RECNO la cual es de tipo entero sin signo y es autoincremental, y adicionalmente creo un indice de tipo Primary key para esta misma columna.

a las columnas con datos únicos, tal como el numero del cliente NUM_CLIE le creo un indice de tipo único.

[joseluisysturiz]

Carlos, buen tips el que distes, me le leido casi todo el manual de mysql y cada vez se consiguen cosas super importante y que nos hacen la programacion mas facil y rapida, saludos...

[FranciscoA]

Talvez esta informacion sea de utilidad.

Code: Select all  Expand view  RUN

OPTIMIZACION DE TABLAS
Para eliminar registros fragmentados y eliminar espacio desperdiciado resultante del
borrado o actualización de registros, ejecute myisamchk en modo recuperación:
shell> myisamchk -r nombre_tabla

Puede optimizar una tabla de la misma forma usando el comando SQL OPTIMIZE TABLE.
OPTIMIZE TABLE realiza una reparación de la tabla y un análisis de las claves,
y también ordena el árbol de índices para obtener un mejor rendimiento en la búsqueda
de claves.
No hay posibilidad de interacción no deseada entre una utilidad y el servidor, ya que
el servidor hace todo el trabajo cuando usa OPTIMIZE TABLE.
Consulte Sección 13.5.2.5, “Sintaxis de OPTIMIZE TABLE”.

myisamchk tiene una serie de opciones que puede usar para mejorar el rendimiento de una tabla:
• -S, --sort-index
• -R index_num, --sort-records=index_num
• -a, --analyze
Para una descripción completa de estas opciones, consulte Sección 5.8.3.1, “Sintaxis para invocar myisamchk”.

//***************************************
Sintaxis de OPTIMIZE TABLE
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

OPTIMIZE TABLE debe usarse si ha borrado una gran parte de la tabla o si ha hecho
varios cambios en una tabla con registros de longitud variable (tablas que tienen
columnas VARCHAR, BLOB, o TEXT ).

Los registros borrados se mantienen en una lista enlazada operaciones INSERT
posteriores reúsan posiciones de antiguos registros.

Puede usar OPTIMIZE TABLE para reclamar el usuario no usado y para defragmentar
el fichero de datos.

En la mayoría de inicializaciones, no necesita ejecutar OPTIMIZE TABLE para nada.
Incluso si hace muchas actualizaciones a registros de longitud variables,no es
probable que necesite hacerlo más de una vez a la semana o mes y sólo en ciertas tablas.

Actualmente, OPTIMIZE TABLE funciona sólo en tablas MyISAM, BDB y InnoDB .

Para tablas MyISAM , OPTIMIZE TABLE funciona como sigue:
1. Si la tabla ha borrado o dividido registros, repare la tabla.
2. Si las páginas índice no están ordenadas, ordénelas.
3. Si las estadísticas no están actualizadas (y la reparación no puede hacerse ordenando el índice), actualícelas.

Para tablas BDB , OPTIMIZE TABLE es mapea como ANALYZE TABLE.

Para tablas InnoDB , se mapea con ALTER TABLE, que reconstruye la tabla.
Reconstruye las estadísticas actualizadas de índice y libera espacio no usado en el
índice clusterizado.

Puede hacer que OPTIMIZE TABLE funcione con otros tipos de tabla arrancando mysqld con la opción --skip-new o -
-safe-mode ; en este caso OPTIMIZE TABLE se mapea con ALTER TABLE.

Tenga en ceunta que MySQL bloquea la tabla mientras se ejecuta OPTIMIZE TABLE .

En MySQL 5.0, los comandos OPTIMIZE TABLE se escriben en el log binario a no ser que
la palabra NO_WRITE_TO_BINLOG opcional(o su alias LOCAL) se use.
Esto se hace para que los comandos OPTIMIZE TABLE se usen en MySQL server actuando
como maestro de replicación se replique por defecto en el esclavo de replicación.
*/

 //---------------------//Optimizacion de tablas
Function Optimizar()
oServer:Query("OPTIMIZE TABLE usuarios,catalogo,comprobd,cheques,minutas,recibcaj")  
return nil
 

[cnavarro]

Carlos, gracias por tus explicaciones
Asi da gusto

[carlos vargas]

agradezco la mayoría de estos conocimientos al blog de walter, el cual ruego encarecidamente que lean,
hay artículos que son una verdadera joya en cuanto a sql y a algunas técnicas de programación,
les aseguro que no tiene desperdicio.

salu2