indices (avanzados)

[emiliodeg]

hola queria consultar q es mas eficiente para poner indices en mysql

supongamos una tabla cualquiera

tabla_usuarios
id
usuario
contraseña
nombre
email
fecha_nac

ahora quiero ponerle indices a los atributos por los q se busca (id, usuario y contraseña) cual seria la forma mas optima (respecto de la consulta no de la actualizacion)

1.- un indice para cada uno de los 3 atributos
2.- un indice de los 3 atritubos


espero me puedan aconsejar
saludos

[BrujoNic]

Entre más grande es el índice, los tiempos de respuesta serán más pesados cuando la tabla sea grande.

Supongo que el decir ponerle clave a contraseña es una simple suposición verdad?

[gnzsoloyo]

Pero si la búsqueda abarca los datos que están solamente en el índice, leerá el índice sin leer la tabla.

El tema es usar un ídice de alta selectivdad. Es decir: ¿Qué conjunto de datos, ante una búsqueda te provee una respuesta de reducido número de registros?

Tip: La tabla usada de ejemplo es muy peligrosa, porque NUNCA se usa una CLAVE para crear un índice.

[emiliodeg]

Iniciado por gnzsoloyo

Tip: La tabla usada de ejemplo es muy peligrosa, porque NUNCA se usa una CLAVE para crear un índice.

¿por que?

[gnzsoloyo]

Es fácilmente accesible y deja rastros en varias partes (logs y buffers incluidos), ya que también se está guardando información en las tablas del InformationSchema.
¿te gustaría que cualquiera que tenga privilegios de acceder a MySQL pueda leer las claves y los nombres de usuario de una aplicación restringida (p.e. control de sueldos y jornales)?

[emiliodeg]

mmmm voy a tener q hacer algunas revisiones entonces

[BrujoNic]

emiliodeg, la razón por la que NO se utiliza como clave un campo clave, es muy sencilla. Si uno hace las cosas bien, las claves van encriptada con la función PASSWORD, MD5 o cualquier cosa.

¿Cómo vas a realizar una búsqueda con algo que está encriptado? es ilógico no?

Ya si te pones a guardar las contraseñas legibles, eso sería muy fácil de violar y la idea de una base de datos, es tener segura la información.

[gnzsoloyo]

Exacto. Estoy suponiendo que no las encriptó, lo que ya constituye un error gravísimo.
Además hay que tener en cuenta que la función PASSWOD() es unidireccional, y ya encriptada no es reconvertible, al menos hasta la versión 5.

[emiliodeg]

las cotnraseñas estan encriptadas con md5

explico un poquito, (es un caso ipotetico) por ejepmlo cuando buscas un usuario para verificar sus datos para logearlos buscas user y pass, si esos campos estan en un index la busqueda deberia ser mas rapida (aunq me dicen q estos datos quedan almacenados en lugares vulenerables)

y si queres buscar un usuarios x para ver sus datos lo buscas por id (por simplicidad aunq tambien se podria buscar por usuario pq es unico) y si el id esta en un index tambien se buscaria mucho mas rapido

en un sitio moderadamente grande con muchos registros en la base de datos las consultas comienzan a relentizarse si no tienen indices

respecto a lo q decis BrujoNic
¿Cómo vas a realizar una búsqueda con algo que está encriptado? es ilógico no?
si el dato ya lo pasas encriptado sirve para buscar

si por post te vienen
user= juan
pass= 1234
y le mandas esto a la bbdd mas vale q no va a encontrar nada pq el pass va encriptado con md5
pero ahora is a la bbdd le pasas, juan y md5(1234) si lo va a encontrar

q les parece???
tienen algun ejemplo propio de unas tablas y sus indices?

[matanga]

Hola,

Estoy un poco fuera de mi area pero este thread se puso bueno.

A mi me gustaria separar las tareas en dos:

1. Buscar el ROWID de la fila en la base de datos.
2. Una vez completo el paso 1, obtener los datos de la fila.

El punto 1 es el que mas tiene que llevar horas de diseño y es el que mas nos va a afectar cuando falle, es decir, esta relacionado directamente con la cantidad de registros de la tabla y con las consultas SQL que hagamos para buscar datos.

Lo primero es saber que tipos de consultas se van a hacer sobre la tabla, por ejemplo

Código:

1. select * from usuarios where id = 100;
2. select * from usuarios where usuario = 'pepe';
3. select * from usuarios;
4. select * from usuarios where contraseña = md5(12345); ????

En principio lo logico para poder encontrar el ROWID es indexar el campo ID o el campo USUARIO, en mi opinion las consultas 1 y 2 son las mas comunes.

Ahora, una vez que tenemos el ROWID, la segunda tarea es, desde donde obtenemos los datos. Este segundo paso no tiene relacion con la cantidad de filas de la tabla, como ya tenemos el ROWID, no importa si la tabla tiene 1000000000 de registros, nosotros solo vamos a trabajar con 1 solo.

Hay dos opciones, o se los lee del indice o de la tabla, pero para que los lea del indice, hay que indexar todos los campos que contemplan la consulta SQL.

En este caso hay que hacer un indice compuesto con los campos ID, NOMBRE

Código:

select id, nombre from usuarios where id = 3;

En este caso hay que hacer un indice compuesto con los campos ID, NOMBRE, CONTRASEÑA

Código:

select id, nombre, contraseña where id = 3;

Un poco de ejemplos para ver como resuelve esto MySql.

Creo una tabla de usuarios y le cargo algunos registros.

Código:

mysql> create table t1 (id numeric, nombre varchar(30));
Query OK, 0 rows affected (0.08 sec)

mysql>
mysql>
mysql> insert into t1 values (1,'nombre1');
Query OK, 1 row affected (0.03 sec)

mysql> insert into t1 values (2,'nombre2');
Query OK, 1 row affected (0.02 sec)

mysql> insert into t1 values (3,'nombre3');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1 values (4,'nombre4');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values (5,'nombre5');
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;
+------+---------+
| id   | nombre  |
+------+---------+
|    1 | nombre1 |
|    2 | nombre2 |
|    3 | nombre3 |
|    4 | nombre4 |
|    5 | nombre5 |
+------+---------+
5 rows in set (0.00 sec)

Creo un indice sobre la tabla en el campo ID y analizo la tabla para generar las estadisticas.

Código:

mysql> create unique index ind_t1_id on t1(id);
Query OK, 5 rows affected (0.06 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> analyze table t1;
+---------+---------+----------+----------+
| Table   | Op      | Msg_type | Msg_text |
+---------+---------+----------+----------+
| bugs.t1 | analyze | status   | OK       |
+---------+---------+----------+----------+
1 row in set (0.00 sec)

Ahora le pido el plan de ejecucion de las dos consultas, una obtiene los datos del indice y la otra de la tabla, esto lo pueden apreciar en el ultimo Texto, EXTRA

Código:

mysql> explain select id from t1 where id = 3;
+----+-------------+-------+-------+---------------+-----------+---------+-------+------+-------------+
| id | select_type | table | type  | possible_keys | key       | key_len | ref   | rows | Extra       |
+----+-------------+-------+-------+---------------+-----------+---------+-------+------+-------------+
|  1 | SIMPLE      | t1    | const | ind_t1_id     | ind_t1_id | 6       | const |    1 | Using index |
+----+-------------+-------+-------+---------------+-----------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from t1 where id = 3;
+----+-------------+-------+-------+---------------+-----------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key       | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+-----------+---------+-------+------+-------+
|  1 | SIMPLE      | t1    | const | ind_t1_id     | ind_t1_id | 6       | const |    1 |       |
+----+-------------+-------+-------+---------------+-----------+---------+-------+------+-------+
1 row in set (0.00 sec)

Lo mas importante es que en las dos consultas se obtiene el ROWID a traves de un indice y no se hace un full-scan sobre la tabla, en esto es en lo que hay que gastar mas energias porque es lo que te va a afecar directamente cuando la tabla se haga grande.

Es practicamente imposoble de medir en tiempos la diferencia entre obtener los datos del indice y obtener los datos de la tabla.

En conclusion,

1. Con indexar el campo ID deberia ser suficiente, digo ID y no USUARIO porque seguro ID es el mas pequeño.
2. Si indexas los 3 campos, y la tabla realmente sera grande, entonces el indice tambien lo sera, y tendras que administrar el espacio de los dos objetos, desventaja mucho mayor que la velocidad de obtener los datos directamente del indice.

Para mas referencia esta este link

http://dev.mysql.com/doc/refman/5.0/en/explain.html


Saludos

[emiliodeg]

es buena esa opcion de buscar primero el id para luego comprobar los datos, pero implicaria o usar 2 consultas o hacer una consulta con subconsulta

voy a preparar un ejemplo con un millon de registros para q hagamos unas pruebas a ver como funciona y despues lo dejo para q los q queiran hacer sus pruebas puedan hacerlo y saquemos algunas conclusiones

saludos

[BrujoNic]

Mira, si a un usuario se le olvida la clave, lo que se hace es sustituirla y cuando ingrese por primera ves, le pida cambiarla.

Uno NO debe saber ni preguntarle la clave a los usuarios. Además, te has puesto a ver el largo que genera MD5 al convertir? es muy grande, o sea, desperdicio de espacio.

No estoy para nada de acuerdo en generar índices con las claves, ya que no va a importar para nada que le ponga el encriptamiento más grande que exista si solo llamo al usuario y le digo "necesito tu clave", disculpa pero es ilógico e incorrecto.

Pero bueno, es tu diseño.

[emiliodeg]

bueno si puede q este mal eso, pero el ejemplo era hipotetico, no es para q se ensañen tanto con lo del pass

[matanga]

Hola,

es buena esa opcion de buscar primero el id para luego comprobar los datos, pero implicaria o usar 2 consultas o hacer una consulta con subconsulta

No, no hay dos consultas, solo una. Lo que intentaba decir es que una unica operacion SQL, un select en este caso, lo podemos separar en dos partes.

Supongamos que quieres obtener los datos de un usuario con la siguiente consulta

Código:

select * from usuarios where id = 3;

Cualquier base de datos, lo que hace primero es ubicar el ROWID que corresponde con la columna ID=3 y despues busca todos los datos de la fila, el SELECT *.

La idea era comentar como buscar en forma eficiente el ROWID y desde donde se leen el resto de los datos de la fila.

Saludos.