3.6. Ejemplos de consultas comunes

Un OR empleando una única clave es bien optimizado, como es el manejo de AND

El único caso difícil es la búsqueda sobre dos diferentes claves combinadas con OR:

SELECT field1_index, field2_index FROM test_table
WHERE field1_index = '1' OR  field2_index = '1'

Esto se ha optimizado a partir de MySQL 5.0.0. Consulte Sección 7.2.6, “Index Merge Optimization”.

En MySQL 5.0 tambien se puede resolver eficientemente este problema utilizando una UNION que combine la salida de dos sentencias SELECT separadas. Consulte Sección 13.2.7.2, “Sintaxis de UNION”.

Cada sentencia SELECT busca en solamente una clave y puede ser optimizada:

SELECT field1_index, field2_index
    FROM test_table WHERE field1_index = '1'
UNION
SELECT field1_index, field2_index
    FROM test_table WHERE field2_index = '1';

El siguiente ejemplo muestra cómo se pueden utilizar las funciones de bits para calcular la cantidad de dias de un mes que un usuario ha visitado una página Web.

CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL,
             day INT(2) UNSIGNED ZEROFILL);
INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2),
            (2000,2,23),(2000,2,23);

La tabla del ejemplo contiene valores de año, mes y dia que representan las visitas de los usuarios a la página. Para determinar en cuántos días diferentes del mes se produjeron las visitas, se emplea esta consulta:

SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1
       GROUP BY year,month;

La cual devuelve:

+------+-------+------+
| year | month | days |
+------+-------+------+
| 2000 |    01 |    3 |
| 2000 |    02 |    2 |
+------+-------+------+

La consulta calcula cuantos días diferentes aparecen en la tabla para cada combinación de año y mes, removiendo automáticamente las entradas duplicadas.

El atributo AUTO_INCREMENT puede utilizarse para generar un identificador único para cada nueva fila:

CREATE TABLE animals (
             id MEDIUMINT NOT NULL AUTO_INCREMENT,
             name CHAR(30) NOT NULL,
             PRIMARY KEY (id)
             );
INSERT INTO animals (name) VALUES ('dog'),('cat'),('penguin'),
                                  ('lax'),('whale'),('ostrich');
SELECT * FROM animals;

Lo cual devuelve:

+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | lax     |
|  5 | whale   |
|  6 | ostrich |
+----+---------+

Para obtener el valor AUTO_INCREMENT más recientemente generado se puede utilizar la funcion SQL LAST_INSERT_ID() o la función del API de C mysql_insert_id(). Estas funciones son específicas de cada conexión, de modo que su valor de retorno no es afectado por las inserciones realizadas a través de otras conexiones.

Nota: Para una inserción de múltiples filas, LAST_INSERT_ID()/mysql_insert_id() retornan el valor AUTO_INCREMENT de la primera de las filas insertadas. Esto permite que las inserciones de múltiples filas sean reproducidas correctamente en otros servidores en una configuración de replicación.

Para tablas MyISAM y BDB se puede especificar AUTO_INCREMENT sobre una columna secundaria en un índice de múltiples columnas. En este caso, el valor generado para la columna AUTO_INCREMENT es calculado como MAX(auto_increment_column)+1 WHERE prefix=given-prefix. Esto es útil cuando se desea colocar datos en grupos ordenados.

CREATE TABLE animals (
             grp ENUM('fish','mammal','bird') NOT NULL,
             id MEDIUMINT NOT NULL AUTO_INCREMENT,
             name CHAR(30) NOT NULL,
             PRIMARY KEY (grp,id)
             );
INSERT INTO animals (grp,name) VALUES('mammal','dog'),('mammal','cat'),
                  ('bird','penguin'),('fish','lax'),('mammal','whale'),
                  ('bird','ostrich');
SELECT * FROM animals ORDER BY grp,id;

Lo cual devuelve:

+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
+--------+----+---------+

Nótese que en este caso (cuando la columna AUTO_INCREMENT es parte de un índice de múltiples columnas), los valores AUTO_INCREMENT son reutilizados si se elimina la fila con el valor AUTO_INCREMENT más alto en cualquier grupo. Esto ocurre incluso para tablas MyISAM, en las que los valores AUTO_INCREMENT normalmente no son reutilizados

Si la columna AUTO_INCREMENT es parte de varios índices, MySQL generará valores secuenciales empleando el índice que comienza con la columna AUTO_INCREMENT, si hay uno. Por ejemplo, si la tabla animals contiene los índices PRIMARY KEY (grp, id) e INDEX (id), MySQL ignoraría el índice PRIMARY KEY al generar valores secuenciales. Como resultado, la tabla contendría una secuencia simple, sin considerar el valor grp.