Al utilizar el motor de almacenamiento MyISAM, MySQL usa un bloqueo (lock) extremadamente rápido de tablas, que permite múltiples lecturas o una sola escritura. El mayor problema con este motor de almacenamiento ocurre cuando se tiene un flujo constante de actualizaciones y selecciones lentas de una sola tabla. Si éste es el problema para algunas tablas, puede usar otro motor de almacenamiento para ellas. Ver Capítulo 14, Motores de almacenamiento de MySQL y tipos de tablas.

MySQL puede trabajar con tablas transaccionales y no transaccionales. Para hacer el trabajo más facil con tablas no transaccionales (donde no se puede deshacer una transacción si algo va mal), MySQL tiene las siguientes reglas. Obsérvese que estas reglas sólo se aplican cuando no se está corriendo en modo SQL estricto o si se usa el especificador IGNORE para un INSERT o un UPDATE.

Si se usa una tabla no transaccional, no debería usar MySQL para comprobar el contenido de la columna. En general, la manera más segura (y generalmente más rápida) es usar la aplicacion para asegurarse que se están pasando sólo valores legales a la base de datos.

Para más informacion acerca de esto, ver Sección 1.7.6, “Cómo trata MySQL las restricciones (Constraints)” y Sección 13.2.4, “Sintaxis de INSERT” o Sección 5.3.2, “El modo SQL del servidor”.

Debido a que todos los servidores SQL implementan diferentes partes del estándar SQL, toma trabajo escribir aplicaciones SQL portables. Es muy fácil obtener portabilidad para selects muy simples y para inserts, pero es más difícil cuantas más funcionalidades se requieran. Obtener una aplicación que sea rápida en varios sistemas de bases de datos, es una tarea muy difícil.

Para hacer aplicaciones complejas portables, necesita determinar para cuáles servidores SQL debe trabajar, después determinar qué características soportan esos servidores.

Todos los sistemas de bases de datos tienen algunos puntos débiles. Esto es, tienen diferentes concesiones de diseño que conducen a comportamientos diferentes.

Puede usar el programa de MySQL crash-me para encontrar funciones, tipos y límites que puede usar con una selección de servidores de bases de datos. crash-me no comprueba cada posible característica, pero es razonablemente completo, puesto que hace cerca de 450 pruebas.

Un ejemplo de un tipo de información de la que el programa crash-me puede proveer es que no debería usar nombres de columnas que sean mayores de 18 caracteres si quiere que la aplicación funcione en Informix o DB2.

El programa crash-me y MySQL benchmarks son independientes de la base de datos. Mirando por encima cómo están escritos, puede hacerse una idea de qué debe hacer que sus propias aplicaciones sean independientes de bases de datos. Los programas se encuentran en el directorio sql-bench dentro del código fuente de la distribución de MySQL. Estan escritos en Perl y usan la interfaz para bases de datos DBI. Usar DBI ya soluciona una parte de los problemas de portabilidad puesto que provee de métodos de acceso independientes a las bases de datos.

Ver http://dev.mysql.com/tech-resources/benchmarks/ para los resultados de las mediciones.

Esforzarse para ser independiente del motor de bases de datos, implica prestar atención a los cuellos de botella de cada servidor SQL. Por ejemplo, MySQL es muy rápido obteniendo y actualizando registros para tablas MyISAM, pero tiene un problema mezclando lecturas y escrituras lentas sobre la misma tabla. Por otra parte, Oracle tiene un enorme problema cuando intenta acceder a registros que se han actualizado recientemente (hasta que se escriben en el disco). Las bases de datos transaccionales en general no son muy buenas generando resúmenes de tablas desde las tablas de registro (log), puesto que en este caso el bloqueo (lock) de registros es casi inútil.

Para hacer una aplicación realmente independiente de la base de datos, se necesita definir una interfaz fácilmente extendible a través de la cual se manipularán los datos. Puesto que C++ está disponible en la mayoría de los sistemas, tiene sentido usar una interfaz basada en clases de C++ hacia la base de datos.

Si usa alguna característica que es específica de algún sistema de base de datos (por ejemplo la sentencia REPLACE, que es específica en MySQL), debería implementar la misma característica para otros servidores SQL codificando un método alternativo. Aunque la alternativa sea más lenta, permite que la misma tarea se haga en otros servidores.

Con MySQL, puede usar la sintaxis /*! */ para agregar algunas palabras claves de MySQL a una consulta. El código dentro de /**/ es tratado como un comentario (e ignorado) por la mayoría de los otros servidores SQL.

Si el alto rendimiento es más importante que la exactitud, como en algunas aplicaciones Web, es posible crear una capa de la aplicación que almacene en una cache todos los resultados para obtener un rendimiento mejor. Dejando que los resultados viejos expiren en determinado tiempo, puede mantener la cache razonablemente actualizado. Éste es un método para soportar picos de carga, al que se añade la posibilidad de implementar un incremento dinámico de la cache y un aumento del tiempo de expiración, hasta que las cosas regresen a la normalidad.

En este caso, la información de creación de la tabla debe contener información del tamaño inicial de la cache y de la frecuencia de refresco de la tabla.

Una alternativa para implementar una aplicación en cache es usar la cache para consultas de MySQL. Habilitando la cache para consultas, el servidor toma los detalles de las consultas determinando qué resultados pueden ser reutilizados. Esto simplifica la aplicación. Ver Sección 5.12, “La caché de consultas de MySQL”.

Esta sección describe un uso inicial de MySQL.

Durante el inicio del desarrollo de MySQL, las características de MySQL fueron pensadas para ajustarse a nuestro cliente más grande, que hacía data-warehouse para un par de los mayoristas más grandes de Suecia.

Desde todas las tiendas obteníamos resúmenes semanales de todas las transacciones con tarjetas de bonos, y se esperaba de nosotros información útil para que los dueños de las tiendas entendieran cómo sus campañas publicitarias afectaban a sus propios clientes.

El volumen de datos era enorme (cerca de siete millones de transacciones resumidas por mes), y teníamos datos de 4-10 años que debíamos presentar a los usuarios. Teníamos peticiones semanales de nuestros clientes, quienes querían acceso instantáneo a los nuevos reportes de esta información.

Solucionamos este problema almacenando toda la información por mes en unas “tablas de transacciones” comprimidas. Teníamos una serie de sencillas macros que generaban resúmenes de tablas agrupados por diferentes criterios (grupo de productos, id del cliente, tienda, etc) a partir de las tablas en las que fueron almacenadas las transacciones. Los reportes eran páginas Web que eran generadas dinámicamente por un pequeño script escrito en Perl. Este script analizaba la página Web, ejecutaba una sentencia SQL y escribía los resultados. Debimos usar PHP o mod_perl en vez de eso, pero no estaban disponibles en aquel entonces.

Para datos gráficos, escribimos una sencilla herramienta en C que podía procesar los resultados de una consulta SQL y generar una imagen GIF basada en los resultados. Esta herramienta también era dinámicamente invocada desde el script en Perl que analizaba las páginas Web.

En la mayoría de casos, se podía crear un nuevo reporte simplemente copiando un script existente y modificando la consulta SQL que utilizaba. En algunos casos, necesitábamos agregar más columnas a una tabla de resumen existente o generar una nueva. Esto también fue sencillo puesto que guardábamos todas las tablas con las transacciones en disco. (El tamaño era de alrededor de 50GB de tablas de transacciones y 200GB de otros datos del cliente.)

También permitimos a nuestros clientes acceder a las tablas de resumen directamente con ODBC, para que los usuarios avanzados pudieran experimentar con los datos por sí mismos.

Este sistema trabajó bien y no tuvimos problemas elaborando los datos en un modesto servidor Sun Ultra SPARCstation (2x200Mhx). Finalmente el sistema fue migrado a Linux.

Esta sección debería contener una descripción técnica del paquete de pruebas de rendimiento de MySQL (así como del crash-me), pero esa descripción aún no ha sido escrita. Sin embargo, puede hacerse una buena idea de cómo hacer pruebas de rendimiento viendo el código y los resultados dentro del directorio sql-bench en el código fuente de la distribución de MySQL.

La finalidad de este paquete de pruebas de rendimiento es visualizar qué operaciones se realizan bien y cuáles lo hacen pobremente en cada implementación de SQL.

Estas pruebas de rendimiento no son multi hilo, así que miden el tiempo mínimo para las operaciones realizadas. Se planea agregar en un futuro pruebas multi hilo al paquete.

Para usar el paquete, deben satisfacerse los siguientes requisitos:

Una vez obtenido el código fuente de la distribución de MySQL, el paquete de pruebas de rendimiento se encuentra en el directorio sql-bench. Para ejecutar las pruebas de rendimiento, compílese MySQL, váyase al directorio sql-bench y ejecútese el script run-all-tests:

shell> cd sql-bench
shell> perl run-all-tests --server=nombre_servidor

nombre_servidor debe ser uno de los servidores soportados. Para obtener la lista completa de opciones y servidores soportados, invóquese el comando:

shell> perl run-all-tests --help

El script crash-me también está situado dentro del directorio sql-bench. crash-me intenta determinar qué características soporta una base de datos y cuáles son sus capacidades y limitaciones. Esto lo consigue ejecutando consultas. Determina por ejemplo:

Para más información acerca de resultados de pruebas de rendimiento, visítese http://dev.mysql.com/tech-resources/benchmarks/.

Decididamente debería realizar pruebas de rendimiento sobre su aplicación y su base de datos para determinar dónde se encuentran los cuellos de botella. Eliminando un cuello de botella (o reemplazándolo con un módulo “tonto”) puede fácilmente identificar el siguiente cuello de botella. Aunque el rendimiento global de su aplicación sea aceptable, debería al menos hacer un plan para cada cuello de botella, y decidir cómo eliminarlo si algún día realmente necesita un rendimiento extra.

Para ejemplos de programas de pruebas de rendimiento portables, mire el paquete de pruebas de rendimiento MySQL. Ver Sección 7.1.4, “El paquete de pruebas de rendimiento (benchmarks) de MySQL”. Puede tomar cualquier programa de este paquete y modificarlo en base a sus necesidades. Así puede probar diferentes soluciones para su problema y probar cuál realmente es más rápida para usted.

Otro paquete de pruebas de rendimiento es Open Source Database Benchmark, disponible en http://osdb.sourceforge.net/.

Es muy común que un problema ocurra sólo cuando el sistema está bajo mucha carga. Muchos de nuestros clientes nos contactan cuando tienen un sistema (probado) en producción y encuentran problemas de carga. En la mayoría de casos, los problemas de rendimiento se deben a cuestiones relacionadas con el diseño básico de la base de datos (por ejemplo, lecturas completas de las tablas no son buenas bajo alta carga) o problemas con el sistema operativo o las bibliotecas. En la mayoría de casos, esos problemas se solucionarían mucho más fácilmente si los sistemas no estuvieran en producción.

Para evitar este tipo de problemas, es conveniente hacer pruebas de rendimiento a las aplicaciones enteras bajo la mayor carga de trabajo posible. Para ello, puede utilizarse Super Smack, disponible en http://jeremy.zawodny.com/mysql/super-smack/. Como su nombre indica, puede poner de rodillas a un sistema, así que asegúrese de usarlo sólo en sistemas de desarrollo.

EXPLAIN nombre_de_tabla

O:

EXPLAIN SELECT opciones_de_select

La sentencia EXPLAIN puede utilizarse como un sinónimo de DESCRIBE o también como una manera para obtener información acerca de cómo MySQL ejecuta una sentencia SELECT:

Esta sección trata sobre el segundo uso de EXPLAIN.

EXPLAIN es una ayuda para decidir qué índices agregar a las tablas, con el fin de que las sentencias SELECT encuentren registros más rápidamente. EXPLAIN puede utilizarse también para verificar si el optimizador une (join) las tablas en el orden óptimo. Si no fuera así, se puede forzar al optimizador a unir las tablas en el orden en el que se especifican en la sentencia SELECT empezando la sentencia con SELECT STRAIGHT_JOIN en vez de simplemente SELECT.

Si un índice no está siendo utilizado por las sentencias SELECT cuando debiera, debe ejecutarse el comando ANALYZE TABLE, a fin de actualizar las estadísticas de la tabla como la cardinalidad de sus claves, que pueden afectar a la decisiones que el optimizador toma. Ver Sección 13.5.2.1, “Sintaxis de ANALYZE TABLE”.

EXPLAIN muestra una línea de información para cada tabla utilizada en la sentencia SELECT. Las tablas se muestran en el mismo orden en el que MySQL las leería al procesar la consulta. MySQL resuelve todas las uniones (joins) usando un método de single-sweep multi-join. Esto significa que MySQL lee un registro de la primera tabla; encuentra su correspondiente en la segunda tabla, en la tercera, y así sucesivamente. Cuando todas las tablas han sido procesadas, MySQL muestra las columnas seleccionadas y recorre a la inversa la lista de tablas hasta que encuentra aquélla para la que la sentencia devuelve más registros. Se lee entonces el siguiente registro de esta tabla y el proceso continúa con la siguiente tabla.

EXPLAIN retorna una tabla; cada línea de esta tabla muestra información acerca de una tabla, y tiene las siguientes columnas:

You can get a good indication of how good a join is by taking the product of the values in the rows column of the EXPLAIN output. This should tell you roughly how many rows MySQL must examine to execute the query. If you restrict queries with the max_join_size system variable, this product also is used to determine which multiple-table SELECT statements to execute. See Sección 7.5.2, “Afinar parámetros del servidor”.

The following example shows how a multiple-table join can be optimized progressively based on the information provided by EXPLAIN.

Suppose that you have the SELECT statement shown here and you plan to examine it using EXPLAIN:

EXPLAIN SELECT tt.TicketNumber, tt.TimeIn,
            tt.ProjectReference, tt.EstimatedShipDate,
            tt.ActualShipDate, tt.ClientID,
            tt.ServiceCodes, tt.RepetitiveID,
            tt.CurrentProcess, tt.CurrentDPPerson,
            tt.RecordVolume, tt.DPPrinted, et.COUNTRY,
            et_1.COUNTRY, do.CUSTNAME
        FROM tt, et, et AS et_1, do
        WHERE tt.SubmitTime IS NULL
            AND tt.ActualPC = et.EMPLOYID
            AND tt.AssignedPC = et_1.EMPLOYID
            AND tt.ClientID = do.CUSTNMBR;

For this example, make the following assumptions:

Initially, before any optimizations have been performed, the EXPLAIN statement produces the following information:

table type possible_keys key  key_len ref  rows  Extra
et    ALL  PRIMARY       NULL NULL    NULL 74
do    ALL  PRIMARY       NULL NULL    NULL 2135
et_1  ALL  PRIMARY       NULL NULL    NULL 74
tt    ALL  AssignedPC,   NULL NULL    NULL 3872
           ClientID,
           ActualPC
      range checked for each record (key map: 35)

Because type is ALL for each table, this output indicates that MySQL is generating a Cartesian product of all the tables; that is, every combination of rows. This takes quite a long time, because the product of the number of rows in each table must be examined. For the case at hand, this product is 74 * 2135 * 74 * 3872 = 45,268,558,720 rows. If the tables were bigger, you can only imagine how long it would take.

One problem here is that MySQL can use indexes on columns more efficiently if they are declared as the same type and size. In this context, VARCHAR and CHAR are considered the same if they are declared as the same size. Since tt.ActualPC is declared as CHAR(10) and et.EMPLOYID is CHAR(15), there is a length mismatch.

To fix this disparity between column lengths, use ALTER TABLE to lengthen ActualPC from 10 characters to 15 characters:

mysql> ALTER TABLE tt MODIFY ActualPC VARCHAR(15);

tt.ActualPC and et.EMPLOYID are both VARCHAR(15). Executing the EXPLAIN statement again produces this result:

table type   possible_keys key     key_len ref         rows    Extra
tt    ALL    AssignedPC,   NULL    NULL    NULL        3872    Using
             ClientID,                                         where
             ActualPC
do    ALL    PRIMARY       NULL    NULL    NULL        2135
      range checked for each record (key map: 1)
et_1  ALL    PRIMARY       NULL    NULL    NULL        74
      range checked for each record (key map: 1)
et    eq_ref PRIMARY       PRIMARY 15      tt.ActualPC 1

This is not perfect, but is much better: The product of the rows values is less by a factor of 74. This version is executed in a couple of seconds.

A second alteration can be made to eliminate the column length mismatches for the tt.AssignedPC = et_1.EMPLOYID and tt.ClientID = do.CUSTNMBR comparisons:

mysql> ALTER TABLE tt MODIFY AssignedPC VARCHAR(15),
    ->                MODIFY ClientID   VARCHAR(15);

EXPLAIN produces the output shown here:

table type   possible_keys key      key_len ref           rows Extra
et    ALL    PRIMARY       NULL     NULL    NULL          74
tt    ref    AssignedPC,   ActualPC 15      et.EMPLOYID   52   Using
             ClientID,                                         where
             ActualPC
et_1  eq_ref PRIMARY       PRIMARY  15      tt.AssignedPC 1
do    eq_ref PRIMARY       PRIMARY  15      tt.ClientID   1

This is almost as good as it can get.

The remaining problem is that, by default, MySQL assumes that values in the tt.ActualPC column are evenly distributed, and that is not the case for the tt table. Fortunately, it is easy to tell MySQL to analyze the key distribution:

mysql> ANALYZE TABLE tt;

The join is perfect, and EXPLAIN produces this result:

table type   possible_keys key     key_len ref           rows Extra
tt    ALL    AssignedPC    NULL    NULL    NULL          3872 Using
             ClientID,                                        where
             ActualPC
et    eq_ref PRIMARY       PRIMARY 15      tt.ActualPC   1
et_1  eq_ref PRIMARY       PRIMARY 15      tt.AssignedPC 1
do    eq_ref PRIMARY       PRIMARY 15      tt.ClientID   1

Note that the rows column in the output from EXPLAIN is an educated guess from the MySQL join optimizer. You should check whether the numbers are even close to the truth. If not, you may get better performance by using STRAIGHT_JOIN in your SELECT statement and trying to list the tables in a different order in the FROM clause.

In most cases, you can estimate the performance by counting disk seeks. For small tables, you can usually find a row in one disk seek (because the index is probably cached). For bigger tables, you can estimate that, using B-tree indexes, you need this many seeks to find a row: log(row_count) / log(index_block_length / 3 * 2 / (index_length + data_pointer_length)) + 1.

In MySQL, an index block is usually 1024 bytes and the data pointer is usually 4 bytes. For a 500,000-row table with an index length of 3 bytes (medium integer), the formula indicates log(500,000)/log(1024/3*2/(3+4)) + 1 = 4 seeks.

This index would require storage of about 500,000 * 7 * 3/2 = 5.2MB (assuming a typical index buffer fill ratio of 2/3), so you probably have much of the index in memory and so need only one or two calls to read data to find the row.

For writes, however, you need four seek requests (as above) to find where to place the new index and normally two seeks to update the index and write the row.

Note that the preceding discussion doesn't mean that your application performance slowly degenerates by log N. As long as everything is cached by the OS or the MySQL server, things become only marginally slower as the table gets bigger. After the data gets too big to be cached, things start to go much slower until your applications are bound only by disk seeks (which increase by log N). To avoid this, increase the key cache size as the data grows. For MyISAM tables, the key cache size is controlled by the key_buffer_size system variable. See Sección 7.5.2, “Afinar parámetros del servidor”.

In general, when you want to make a slow SELECT ... WHERE query faster, the first thing to check is whether you can add an index. All references between different tables should usually be done with indexes. You can use the EXPLAIN statement to determine which indexes are used for a SELECT. See Sección 7.4.5, “Cómo utiliza MySQL los índices” and Sección 7.2.1, “Sintaxis de EXPLAIN (Obtener información acerca de un SELECT)”.

Some general tips for speeding up queries on MyISAM tables:

This section discusses optimizations that can be made for processing WHERE clauses. The examples use SELECT statements, but the same optimizations apply for WHERE clauses in DELETE and UPDATE statements.

Note that work on the MySQL optimizer is ongoing, so this section is incomplete. MySQL does many optimizations, not all of which are documented here.

Some of the optimizations performed by MySQL are listed here:

Some examples of queries that are very fast:

SELECT COUNT(*) FROM tbl_name;

SELECT MIN(key_part1),MAX(key_part1) FROM tbl_name;

SELECT MAX(key_part2) FROM tbl_name
    WHERE key_part1=constant;

SELECT ... FROM tbl_name
    ORDER BY key_part1,key_part2,... LIMIT 10;

SELECT ... FROM tbl_name
    ORDER BY key_part1 DESC, key_part2 DESC, ... LIMIT 10;

The following queries are resolved using only the index tree, assuming that the indexed columns are numeric:

SELECT key_part1,key_part2 FROM tbl_name WHERE key_part1=val;

SELECT COUNT(*) FROM tbl_name
    WHERE key_part1=val1 AND key_part2=val2;

SELECT key_part2 FROM tbl_name GROUP BY key_part1;

The following queries use indexing to retrieve the rows in sorted order without a separate sorting pass:

SELECT ... FROM tbl_name
    ORDER BY key_part1,key_part2,... ;

SELECT ... FROM tbl_name
    ORDER BY key_part1 DESC, key_part2 DESC, ... ;

The range access method uses a single index to retrieve a subset of table records that are contained within one or several index value intervals. It can be used for a single-part or multiple-part index. A detailed description of how intervals are extracted from the WHERE clause is given in the following sections.

SELECT * FROM t1 WHERE key_col > 1 AND key_col < 10;

SELECT * FROM t1 WHERE key_col = 1 OR key_col IN (15,18,20);

SELECT * FROM t1 WHERE key_col LIKE 'ab%' OR key_col BETWEEN
'bar' AND 'foo';

SELECT * FROM t1 WHERE
   (key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR
   (key1 < 'bar' AND nonkey = 4) OR
   (key1 < 'uux' AND key1 > 'z');

key_part1  key_part2  key_part3
  NULL       1          'abc'
  NULL       1          'xyz'
  NULL       2          'foo'
   1         1          'abc'
   1         1          'xyz'
   1         2          'abc'
   2         1          'aaa'

(1, -inf, -inf) <= (key_part1, key_part2, key_part3) < (1, +inf, +inf)

The Index Merge (index_merge) method is used to retrieve rows with several ref, ref_or_null, or range scans and merge the results into one. This method is employed when the table condition is a disjunction of conditions for which ref, ref_or_null, or range could be used with different keys.

Note: If you have upgraded from a previous version of MySQL, you should be aware that this type of join optimization is first introduced in MySQL 5.0, and represents a significant change in behavior with regard to indexes. Formerly, MySQL was able to use at most only one index for each referenced table.

In EXPLAIN output, this method appears as index_merge in the type column. In this case, the key column contains a list of indexes used, and key_len contains a list of the longest key parts for those indexes.

Examples:

SELECT * FROM tbl_name WHERE key_part1 = 10 OR key_part2 = 20;

SELECT * FROM tbl_name
    WHERE (key_part1 = 10 OR key_part2 = 20) AND non_key_part=30;

SELECT * FROM t1, t2
    WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE 'value%')
    AND t2.key1=t1.some_col;

SELECT * FROM t1, t2
    WHERE t1.key1=1
    AND (t2.key1=t1.some_col OR t2.key2=t1.some_col2);

The Index Merge method has several access algorithms (seen in the Extra field of EXPLAIN output):

The following sections describe these methods in greater detail.

Note: The Index Merge optimization algorithm has the following known deficiencies:

The choice between different possible variants of the index_merge access method and other access methods is based on cost estimates of various available options.

SELECT * FROM innodb_table WHERE primary_key < 10 AND key_col1=20;

SELECT * FROM tbl_name
WHERE (key1_part1=1 AND key1_part2=2) AND key2=2;

SELECT COUNT(*) FROM t1 WHERE key1=1 AND key2=1;

SELECT * FROM t1 WHERE key1=1 OR key2=2 OR key3=3;

SELECT * FROM innodb_table WHERE (key1=1 AND key2=2) OR
  (key3='foo' AND key4='bar') AND key5=5;

SELECT * FROM tbl_name WHERE key_col1 < 10 OR key_col2 < 20;

SELECT * FROM tbl_name
     WHERE (key_col1 > 10 OR key_col2 = 20) AND nonkey_col=30;

MySQL can perform the same optimization on col_name IS NULL that it can use with col_name = constant_value. For example, MySQL can use indexes and ranges to search for NULL with IS NULL.

SELECT * FROM tbl_name WHERE key_col IS NULL;

SELECT * FROM tbl_name WHERE key_col <=> NULL;

SELECT * FROM tbl_name
    WHERE key_col=const1 OR key_col=const2 OR key_col IS NULL;

If a WHERE clause includes a col_name IS NULL condition for a column that is declared as NOT NULL, that expression is optimized away. This optimization does not occur in cases when the column might produce NULL anyway; for example, if it comes from a table on the right side of a LEFT JOIN.

MySQL 5.0 can also optimize the combination col_name = expr AND col_name IS NULL, a form that is common in resolved subqueries. EXPLAIN shows ref_or_null when this optimization is used.

This optimization can handle one IS NULL for any key part.

Some examples of queries that are optimized, assuming that there is an index on columns a and b of table t2:

SELECT * FROM t1 WHERE t1.a=expr OR t1.a IS NULL;

SELECT * FROM t1, t2 WHERE t1.a=t2.a OR t2.a IS NULL;

SELECT * FROM t1, t2
    WHERE (t1.a=t2.a OR t2.a IS NULL) AND t2.b=t1.b;

SELECT * FROM t1, t2
    WHERE t1.a=t2.a AND (t2.b=t1.b OR t2.b IS NULL);

SELECT * FROM t1, t2
    WHERE (t1.a=t2.a AND t2.a IS NULL AND ...)
    OR (t1.a=t2.a AND t2.a IS NULL AND ...);

ref_or_null works by first doing a read on the reference key, and then a separate search for rows with a NULL key value.

Note that the optimization can handle only one IS NULL level. In the following query, MySQL uses key lookups only on the expression (t1.a=t2.a AND t2.a IS NULL) and is not able to use the key part on b:

SELECT * FROM t1, t2
     WHERE (t1.a=t2.a AND t2.a IS NULL)
     OR (t1.b=t2.b AND t2.b IS NULL);

DISTINCT combined with ORDER BY needs a temporary table in many cases.

Note that because DISTINCT may use GROUP BY, you should be aware of how MySQL works with columns in ORDER BY or HAVING clauses that are not part of the selected columns. See Sección 12.10.3, “GROUP BY con campos escondidos”.

In most cases, a DISTINCT clause can be considered as a special case of GROUP BY. For example, the following two queries are equivalent:

SELECT DISTINCT c1, c2, c3 FROM t1 WHERE c1 > const;

SELECT c1, c2, c3 FROM t1 WHERE c1 > const GROUP BY c1, c2, c3;

Due to this equivalence, the optimizations applicable to GROUP BY queries can be also applied to queries with a DISTINCT clause. Thus, for more details on the optimization possibilities for DISTINCT queries, see Sección 7.2.11, “Cómo optimiza MySQL los GROUP BY”.

When combining LIMIT row_count with DISTINCT, MySQL stops as soon as it finds row_count unique rows.

If you don't use columns from all tables named in a query, MySQL stops scanning the not-used tables as soon as it finds the first match. In the following case, assuming that t1 is used before t2 (which you can check with EXPLAIN), MySQL stops reading from t2 (for any particular row in t1) when the first row in t2 is found:

SELECT DISTINCT t1.a FROM t1, t2 where t1.a=t2.a;

An A LEFT JOIN B join_condition is implemented in MySQL as follows: