Optimizar Consultas SQL

[Canuto]

Aveces el gran cuello de botella es la forma en que estan planteadas las consultas SQL, por ejemplo:

Select * From Tabla , este es el error tipico de un desarrollador. Esta consulta le toma al motor mas tiempo que hacer una consulta tipo Select campo1,campo2,campo3 from Tabla. Debido a que al hacer un "select * from tabla" lo que hace el motor primero es hacer un DESCRIBE Table, para luego el mismo hacer el Select Campo1,Campo2 ... from table .
Otro error comun es no usar los binding en las consultas SQL, me explico

Select Campo1,Campo2 From Tabla where campo1 = 2
Select Campo1,Campo2 From Tabla where campo1 = 3

En los dos casos anteriores,para el motor SQL las dos sentencias son completamente diferentes y compila dos veces la consulta por mas que sean similares. Un motor de base de datos guarda en memoria cierto numero de consultas, para no tener que realizar siempre la compilacion de las mismas.
La solucion a esto es muy simple usando las binding variables un ejemplo seria mas o menos asi

Select Campo1,Campo2 From Tabla where campo1 =:x

donde "X" puedo reemplazarlo por "n" valores y el motor de base de datos entendera que es la misma consulta, y no gastara recursos tontamente volviendo a compilar la consulta.

En oracle el uso de binding variables esta por defecto, para mysql y postgres depende mucho que en el servidor esten levantados los modulos PDO_MYSQL y PDO_PGSQL

:P

[pepeluty]

Otra genial idea de optimizar una búsqueda sql en una tabla de muchos registros es la creación de índices por ejemplo:

Una clave primaria en una tabla es un índice natural de valor único, pero puede pasar que nuestras búsquedas no sean acorde con las claves primarias y sean por nombres, apellidos, números de teléfono etc. Al realizar una consulta en base a un “nombre” o parte de él, la tabla será leída de forma total por el motor de base de datos buscando en cada registro el nombre en particular. Esto es un gran esfuerzo para el motor de DB cuando hablamos de miles y miles de registros, los índices es la solución.

Ejemplo de creación de indicen en una tabla (Mysql en caso de ragnarok)

CREATE INDEX [nombre de índice]
ON [nombre de la tabla] (campo de la tabla);

Con esto el motor agrupara los registros del campo de la tabla (en este caso nombre) de forma numérica, alfabética o alfanumérica creando una categorización ej:

Si se desea buscar el nombre “jose” lo buscara en los registros marcados en negrita, los demás los ignorara:

Usuario (nombre de la tabla)
      Nombre (campo de la tabla)
              Jose
              Jorge
              Juan
              Alberto
              Aldo
              claudio

Los índices pueden ser agrupados con distintos campos. No es aconsejable crear índices si la tabla posee pocos registros

[josezzz]

Que bien, gracias por la ayuda

[Canuto]

Ojo, no es bueno crear varios indices. solo los necesarios. En el caso de mysql para buscar cadenas existe un tipo de indice llamado full text. Pero solo esta disponible para tablas del tipo Myisan (tablas no relacionales). Tambien para relacionar tablas en una consulta es bueno usar "join" y no la simple relacion where a.campo = b.campo, por lo general where se debe usar unicamente opara filtrar data mas no para relacionar tablas.

[Mical]

Muy buena guia gracias ^^

[alazar]

Mmm, ¿alguien puede poner una página donde se explique eso de los bindings en detalle? Gracias^^

[Konoka]

Muy interesante, la verdad.

[Arikel]

Bueno no importara mucho si reavivo el tema ya que esta sección no se usa casi, en la sección staff había unas guías sobre esto que hizo Xati creo recordar, si alguien quiere mover el tema aquí le servira de ayuda a quien lo necesite.

[WikiTek]

Otra genial idea de optimizar una búsqueda sql en una tabla de muchos registros es la creación de índices por ejemplo:

Una clave primaria en una tabla es un índice natural de valor único, pero puede pasar que nuestras búsquedas no sean acorde con las claves primarias y sean por nombres, apellidos, números de teléfono etc. Al realizar una consulta en base a un “nombre” o parte de él, la tabla será leída de forma total por el motor de base de datos buscando en cada registro el nombre en particular. Esto es un gran esfuerzo para el motor de DB cuando hablamos de miles y miles de registros, los índices es la solución.

Ejemplo de creación de indicen en una tabla (Mysql en caso de ragnarok)

CREATE INDEX [nombre de índice]
ON [nombre de la tabla] (campo de la tabla);

Con esto el motor agrupara los registros del campo de la tabla (en este caso nombre) de forma numérica, alfabética o alfanumérica creando una categorización ej:

Si se desea buscar el nombre “jose” lo buscara en los registros marcados en negrita, los demás los ignorara:

Usuario (nombre de la tabla)
      Nombre (campo de la tabla)
              Jose
              Jorge
              Juan
              Alberto
              Aldo
              claudio

Los índices pueden ser agrupados con distintos campos. No es aconsejable crear índices si la tabla posee pocos registros

No se recomienda hacer una llave compuesta con una palabra, le pega mucho al performance.
Ya que estoy aquí, eviten el uso del distinct o de querys anidados, pero a veces se necesitan :(.

Ojo, no es bueno crear varios indices. solo los necesarios. En el caso de mysql para buscar cadenas existe un tipo de indice llamado full text. Pero solo esta disponible para tablas del tipo Myisan (tablas no relacionales). También para relacionar tablas en una consulta es bueno usar "join" y no la simple relacion where a.campo = b.campo, por lo general where se debe usar únicamente para filtrar data mas no para relacionar tablas.

Sin ofender, el consejo es retorico, en todo momento sólo se debe crear lo necesario, pero se deben crear tantos indices de acuerdo a los parámetros de búsqueda que se quiera tener.

Un consejo muy útil y que los de la universidad me van a tachar de loco es de:
Las reglas normales NO siempre aplican, cuando estén manejando millones de registros. En la teoría funciona muy bonito, pero cuando hablamos de 100 o 200 millones de registros, hacer una búsqueda normalizada puede llevarte unos minutos y sin normalizar 1~5 segundos.
Traten de no usar tablas temporales, si son bonitas, son fáciles de usar, pero son innecesarias en la mayoría de los casos, por regla de estandares, se debe de evitar, pero esto no es una ley, sólo es un consejo de acuerdo con las mejores practicas de CMMI.

Fuente: YO
DB2 Database Analyst
Oracle Certified Master