7.2. Optimizar sentencias SELECT y otras consultas

MySQL 5.0

previous page next page

7.2. Optimizar sentencias SELECT y otras consultas

First, one factor affects all statements: The more complex your permission setup is, the more overhead you have.

Using simpler permissions when you issue statements enables MySQL to reduce permission-checking overhead when clients execute statements. For example, if you don't grant any table-level or column-level privileges, the server need not ever check the contents of the and tables. Similarly, if you place no resource limits on any accounts, the server does not have to perform resource counting. If you have a very high query volume, it may be worth the time to use a simplified grant structure to reduce permission-checking overhead.

If your problem is with a specific MySQL expression or function, you can use the function from the mysql client program to perform a timing test. Its syntax is ,). For example: 

mysql> SELECT BENCHMARK(1000000,1+1);
+------------------------+
| BENCHMARK(1000000,1+1) |
+------------------------+
|                      0 |
+------------------------+
1 row in set (0.32 sec)

This result was obtained on a Pentium II 400MHz system. It shows that MySQL can execute 1,000,000 simple addition expressions in 0.32 seconds on that system.

All MySQL functions should be highly optimized, but there may be some exceptions. is an excellent tool for finding out if this is a problem with your query.

7.2.1. Sintaxis de EXPLAIN (Obtener información acerca de un SELECT)

EXPLAIN

Or: 

EXPLAIN SELECT

The statement can be used either as a synonym for or as a way to obtain information about how MySQL executes a statement:

  • is synonymous with or .

  • When you precede a statement with the keyword , MySQL explains how it would process the , providing information about how tables are joined and in which order.

This section provides information about the second use of .

With the help of , you can see where you should add indexes to tables ib order to get a faster that uses indexes to find records.

If you have a problem with incorrect index usage, you should run to update table statistics such as cardinality of keys, which can affect the choices the optimizer makes. See Sección 13.5.2.1, “Sintaxis de ”.

You can also see whether the optimizer joins the tables in an optimal order. To force the optimizer to use a join order corresponding to the order in which the tables are named in the statement, begin the statement with rather than just .

returns a row of information for each table used in the statement. The tables are listed in the output in the order that MySQL would read them while processing the query. MySQL resolves all joins using a single-sweep multi-join method. This means that MySQL reads a row from the first table, then finds a matching row in the second table, then in the third table, and so on. When all tables are processed, MySQL outputs the selected columns and backtracks through the table list until a table is found for which there are more matching rows. The next row is read from this table and the process continues with the next table.

Each output row from provides information about one table, and each row consists of the following columns:

  • The identifier. This is the sequential number of the within the query.

  • The type of , which can be any of the following:

    • Simple (not using or subqueries)

    • Outermost

    • Second or later statement in a

    • Second or later statement in a , dependent on outer query

    • Result of a .

    • First in subquery

    • First in subquery, dependent on outer query

    • Derived table (subquery in clause)

  • The table to which the row of output refers.

  • The join type. The different join types are listed here, ordered from the best type to the worst:

    • The table has only one row (= system table). This is a special case of the join type.

    • The table has at most one matching row, which is read at the start of the query. Because there is only one row, values from the column in this row can be regarded as constants by the rest of the optimizer. tables are very fast because they are read only once.

      is used when you compare all parts of a or index with constant values. In the following queries, can be used as a table: 

      SELECT * FROM  WHERE =1;

SELECT * FROM 
WHERE =1 AND =2;

    • One row is read from this table for each combination of rows from the previous tables. Other than the types, this is the best possible join type. It is used when all parts of an index are used by the join and the index is a or index.

      can be used for indexed columns that are compared using the operator. The comparison value can be a constant or an expression that uses columns from tables that are read before this table.

      In the following examples, MySQL can use an join to process : 

      SELECT * FROM ,
WHERE .=.;

SELECT * FROM ,
WHERE .=.
AND .=1;

    • All rows with matching index values are read from this table for each combination of rows from the previous tables. is used if the join uses only a leftmost prefix of the key or if the key is not a or index (in other words, if the join cannot select a single row based on the key value). If the key that is used matches only a few rows, this is a good join type.

      can be used for indexed columns that are compared using the or operator.

      In the following examples, MySQL can use a join to process : 

      SELECT * FROM  WHERE =;

SELECT * FROM ,
WHERE .=.;

SELECT * FROM ,
WHERE .=.
AND .=1;

    • This join type is like , but with the addition that MySQL does an extra search for rows that contain values. This join type optimization is used most often in resolving subqueries.

      In the following examples, MySQL can use a join to process : 

      SELECT * FROM 
WHERE = OR  IS NULL;

      See Sección 7.2.7, “Cómo optimiza MySQL ”.

    • This join type indicates that the Index Merge optimization is used. In this case, the column contains a list of indexes used, and contains a list of the longest key parts for the indexes used. For more information, see Sección 7.2.6, “Index Merge Optimization”.

    • This type replaces for some subqueries of the following form: 

       IN (SELECT  FROM  WHERE )

      is just an index lookup function that replaces the subquery completely for better efficiency.

    • This join type is similar to . It replaces subqueries, but it works for non-unique indexes in subqueries of the following form: 

       IN (SELECT  FROM  WHERE )

    • Only rows that are in a given range are retrieved, using an index to select the rows. The column indicates which index is used. The contains the longest key part that was used. The column is for this type.

      can be used for when a key column is compared to a constant using any of the , , , , , , , , , or operators: 

      SELECT * FROM 
WHERE  = 10;

SELECT * FROM 
WHERE  BETWEEN 10 and 20;

SELECT * FROM 
WHERE  IN (10,20,30);

SELECT * FROM 
WHERE = 10 AND  IN (10,20,30);

    • This join type is the same as , except that only the index tree is scanned. This usually is faster than , because the index file usually is smaller than the data file.

      MySQL can use this join type when the query uses only columns that are part of a single index.

    • A full table scan is done for each combination of rows from the previous tables. This is normally not good if the table is the first table not marked , and usually very bad in all other cases. Normally, you can avoid by adding indexes that allow row retrieval from the table based on constant values or column values from earlier tables.

  • The column indicates which indexes MySQL could use to find the rows in this table. Note that this column is totally independent of the order of the tables as displayed in the output from . That means that some of the keys in might not be usable in practice with the generated table order.

    If this column is , there are no relevant indexes. In this case, you may be able to improve the performance of your query by examining the clause to see whether it refers to some column or columns that would be suitable for indexing. If so, create an appropriate index and check the query with again. See Sección 13.1.2, “Sintaxis de ”.

    To see what indexes a table has, use .

  • The column indicates the key (index) that MySQL actually decided to use. The key is if no index was chosen. To force MySQL to use or ignore an index listed in the column, use , , or in your query. See Sección 13.2.7, “Sintaxis de ”.

    For and tables, running helps the optimizer choose better indexes. For tables, myisamchk --analyze does the same. See Sección 13.5.2.1, “Sintaxis de ” and Sección 5.8.3, “Mantenimiento de tablas y recuperación de un fallo catastrófico (crash)”.

  • The column indicates the length of the key that MySQL decided to use. The length is if the column says . Note that the value of allows you to determine how many parts of a multiple-part key MySQL actually uses.

  • The column shows which columns or constants are used with the to select rows from the table.

  • The column indicates the number of rows MySQL believes it must examine to execute the query.

  • This column contains additional information about how MySQL resolves the query. Here is an explanation of the different text strings that can appear in this column:

    • MySQL stops searching for more rows for the current row combination after it has found the first matching row.

    • MySQL was able to do a optimization on the query and does not examine more rows in this table for the previous row combination after it finds one row that matches the criteria.

      Here is an example of the type of query that can be optimized this way: 

      SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id
WHERE t2.id IS NULL;

      Assume that is defined as . In this case, MySQL scans and looks up the rows in using the values of . If MySQL finds a matching row in , it knows that can never be , and does not scan through the rest of the rows in that have the same value. In other words, for each row in , MySQL needs to do only a single lookup in , regardless of how many rows actually match in .

    • MySQL found no good index to use, but found that some of indexes might be used once column values from preceding tables are known. For each row combination in the preceding tables, MySQL checks whether it is possible to use a or access method to retrieve rows. The applicability criteria are as described in Sección 7.2.5, “Optimización de rango” and Sección 7.2.6, “Index Merge Optimization”, with the exception that all column values for the preceding table are known and considered to be constants.

      This is not very fast, but is faster than performing a join with no index at all.

    • MySQL needs to do an extra pass to find out how to retrieve the rows in sorted order. The sort is done by going through all rows according to the join type and storing the sort key and pointer to the row for all rows that match the clause. The keys then are sorted and the rows are retrieved in sorted order. See Sección 7.2.10, “Cómo optimiza MySQL ”.

    • The column information is retrieved from the table using only information in the index tree without having to do an additional seek to read the actual row. This strategy can be used when the query uses only columns that are part of a single index.

    • To resolve the query, MySQL needs to create a temporary table to hold the result. This typically happens if the query contains and clauses that list columns differently.

    • A clause is used to restrict which rows to match against the next table or send to the client. Unless you specifically intend to fetch or examine all rows from the table, you may have something wrong in your query if the value is not and the table join type is or .

      If you want to make your queries as fast as possible, you should look out for values of and .

    • , ,

      These indicate how index scans are merged for the join type. See Sección 7.2.6, “Index Merge Optimization” for more information.

    • Similar to the way of accessing a table, indicates that MySQL found an index that can be used to retrieve all columns of a or query without any extra disk access to the actual table. Additionally, the index is used in the most efficient way so that for each group, only a few index entries are read. For details, see Sección 7.2.11, “Cómo optimiza MySQL los ”.

You can get a good indication of how good a join is by taking the product of the values in the column of the output. This should tell you roughly how many rows MySQL must examine to execute the query. If you restrict queries with the system variable, this product also is used to determine which multiple-table 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 .

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

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:

  • The columns being compared have been declared as follows:

    Table Column Column Type

  • The tables have the following indexes:

    Table Index
    (primary key)
    (primary key)

  • The values are not evenly distributed.

Initially, before any optimizations have been performed, the 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 is 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 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, and are considered the same if they are declared as the same size. Since is declared as and is , there is a length mismatch.

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

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

and are both . Executing the 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 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 and comparisons: 

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

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 column are evenly distributed, and that is not the case for the table. Fortunately, it is easy to tell MySQL to analyze the key distribution: 

mysql> ANALYZE TABLE tt;

The join is perfect, and 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 column in the output from 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 in your statement and trying to list the tables in a different order in the clause.

7.2.2. Estimar el renidimiento de una consulta

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( / 3 * 2 / ( + )) + 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 = 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 . 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 ). To avoid this, increase the key cache size as the data grows. For tables, the key cache size is controlled by the system variable. See Sección 7.5.2, “Afinar parámetros del servidor”.

7.2.3. Velocidad de las consultas SELECT

In general, when you want to make a slow 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 statement to determine which indexes are used for a . See Sección 7.4.5, “Cómo utiliza MySQL los índices” and Sección 7.2.1, “Sintaxis de (Obtener información acerca de un )”.

Some general tips for speeding up queries on tables:

  • To help MySQL better optimize queries, use or run myisamchk --analyze on a table after it has been loaded with data. This updates a value for each index part that indicates the average number of rows that have the same value. (For unique indexes, this is always 1.) MySQL uses this to decide which index to choose when you join two tables based on a non-constant expression. You can check the result from the table analysis by using and examining the value. myisamchk --description --verbose shows index distribution information.

  • To sort an index and data according to an index, use myisamchk --sort-index --sort-records=1 (if you want to sort on index 1). This is a good way to make queries faster if you have a unique index from which you want to read all records in order according to the index. Note that the first time you sort a large table this way, it may take a long time.

7.2.4. Optimización de las cláusulas WHERE por parte de MySQL

This section discusses optimizations that can be made for processing clauses. The examples use statements, but the same optimizations apply for clauses in and 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:

  • Removal of unnecessary parentheses: 

       ((a AND b) AND c OR (((a AND b) AND (c AND d))))
-> (a AND b AND c) OR (a AND b AND c AND d)

  • Constant folding: 

       (a<b AND b=c) AND a=5
-> b>5 AND b=c AND a=5

  • Constant condition removal (needed because of constant folding): 

       (B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6)
-> B=5 OR B=6

  • Constant expressions used by indexes are evaluated only once.

  • on a single table without a is retrieved directly from the table information for and tables. This is also done for any expression when used with only one table.

  • Early detection of invalid constant expressions. MySQL quickly detects that some statements are impossible and returns no rows.

  • is merged with if you don't use or group functions (, , and so on).

  • For each table in a join, a simpler is constructed to get a fast evaluation for the table and also to skip records as soon as possible.

  • All constant tables are read first before any other tables in the query. A constant table is any of the following:

    • An empty table or a table with one row.

    • A table that is used with a clause on a or a index, where all index parts are compared to constant expressions and are defined as .

    All of the following tables are used as constant tables: 

    SELECT * FROM t WHERE =1;
SELECT * FROM t1,t2
    WHERE t1.=1 AND t2.=t1.id;

  • The best join combination for joining the tables is found by trying all possibilities. If all columns in and clauses come from the same table, that table is preferred first when joining.

  • If there is an clause and a different clause, or if the or contains columns from tables other than the first table in the join queue, a temporary table is created.

  • If you use , MySQL uses an in-memory temporary table.

  • Each table index is queried, and the best index is used unless the optimizer believes that it is more efficient to use a table scan. At one time, a scan was used based on whether the best index spanned more than 30% of the table. The optimizer is more complex and bases its estimate on additional factors such as table size, number of rows, and I/O block size, so a fixed percentage no longer determines the choice between using an index or a scan.

  • In some cases, MySQL can read rows from the index without even consulting the data file. If all columns used from the index are numeric, only the index tree is used to resolve the query.

  • Before each record is output, those that do not match the clause are skipped.

Some examples of queries that are very fast: 

SELECT COUNT(*) FROM ;

SELECT MIN(),MAX() FROM ;

SELECT MAX() FROM 
    WHERE =;

SELECT ... FROM 
    ORDER BY ,,... LIMIT 10;

SELECT ... FROM 
    ORDER BY  DESC,  DESC, ... LIMIT 10;

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

SELECT , FROM  WHERE =;

SELECT COUNT(*) FROM 
    WHERE = AND =;

SELECT  FROM  GROUP BY ;

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

SELECT ... FROM 
    ORDER BY ,,... ;

SELECT ... FROM 
    ORDER BY  DESC,  DESC, ... ;

7.2.5. Optimización de rango

The 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 clause is given in the following sections.

7.2.5.1. Método de acceso a rango para índices simples

For a single-part index, index value intervals can be conveniently represented by corresponding conditions in the clause, so we'll talk about range conditions rather than “intervals”.

The definition of a range condition for a single-part index is as follows:

  • For both and indexes, comparison of a key part with a constant value is a range condition when using the , , , , or operators.

  • For indexes, comparison of a key part with a constant value is a range condition when using the , , , , , , or operators, or ' (where ' doesn't start with a wildcard).

  • For all types of indexes, multiple range conditions combined with or form a range condition.

“Constant value” in the preceding descriptions means one of the following:

  • A constant from the query string

  • A column of a or table from the same join

  • The result of an uncorrelated subquery

  • Any expression composed entirely from subexpressions of the preceding types

Here are some examples of queries with range conditions in the clause: 

SELECT * FROM t1 WHERE  > 1 AND  < 10;

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

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

Note that some non-constant values may be converted to constants during the constant propagation phase.

MySQL tries to extract range conditions from the clause for each of the possible indexes. During the extraction process, conditions that can't be used for constructing the range condition are dropped, conditions that produce overlapping ranges are combined, and conditions that produce empty ranges are removed.

For example, consider the following statement, where is an indexed column and is not indexed: 

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');

The extraction process for key is as follows:

  1. Start with original clause: 

    (key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR
(key1 < 'bar' AND nonkey = 4) OR
(key1 < 'uux' AND key1 > 'z')

  2. Remove and because they cannot be used for a range scan. The right way to remove them is to replace them with , so that we don't miss any matching records when doing the range scan. Having replaced them with , we get: 

    (key1 < 'abc' AND (key1 LIKE 'abcde%' OR TRUE)) OR
(key1 < 'bar' AND TRUE) OR
(key1 < 'uux' AND key1 > 'z')

  3. Collapse conditions that are always true or false:

    • is always true

    • is always false

    Replacing these conditions with constants, we get: 

    (key1 < 'abc' AND TRUE) OR (key1 < 'bar' AND TRUE) OR (FALSE)

    Removing unnecessary and constants, we obtain 

    (key1 < 'abc') OR (key1 < 'bar')

  4. Combining overlapping intervals into one yields the final condition to be used for the range scan: 

    (key1 < 'bar')

In general (and as demonstrated in the example), the condition used for a range scan is less restrictive than the clause. MySQL performs an additional check to filter out rows that satisfy the range condition but not the full clause.

The range condition extraction algorithm can handle nested / constructs of arbitrary depth, and its output doesn't depend on the order in which conditions appear in clause.

7.2.5.2. Range Access Method for Multiple-Part Indexes

Range conditions on a multiple-part index are an extension of range conditions for a single-part index. A range condition on a multiple-part index restricts index records to lie within one or several key tuple intervals. Key tuple intervals are defined over a set of key tuples, using ordering from the index.

For example, consider a multiple-part index defined as , , ), and the following set of key tuples listed in key order: 

    
  NULL       1          'abc'
  NULL       1          'xyz'
  NULL       2          'foo'
   1         1          'abc'
   1         1          'xyz'
   1         2          'abc'
   2         1          'aaa'

The condition = 1 defines this interval: 

(1, -inf, -inf) <= (, , ) < (1, +inf, +inf)

The interval covers the 4th, 5th, and 6th tuples in the preceding data set and can be used by the range access method.

By contrast, the condition = 'abc' does not define a single interval and cannot be used by the range access method.

The following descriptions indicate how range conditions work for multiple-part indexes in greater detail.

  • For indexes, each interval containing identical values can be used. This means that the interval can be produced only for conditions in the following form: 

          
AND   
AND ...
AND   ;

    Here, , , ... are constants, is one of the , , or comparison operators, and the conditions cover all index parts. (That is, there are conditions, one for each part of an -part index.)

    See Sección 7.2.5.1, “Método de acceso a rango para índices simples” for the definition of what is considered to be a constant.

    For example, the following is a range condition for a three-part index: 

     = 1 AND  IS NULL AND  = 'foo'

  • For a index, an interval might be usable for conditions combined with , where each condition compares a key part with a constant value using , , , , , , , , , , or ' (where ' does not start with a wildcard). An interval can be used as long as it is possible to determine a single key tuple containing all records that match the condition (or two intervals if or is used). For example, for this condition: 

     = 'foo' AND  >= 10 AND  > 10

    The single interval is: 

    ('foo', 10, 10)
   < (, , )
      < ('foo', +inf, +inf)

    It is possible that the created interval contains more records than the initial condition. For example, the preceding interval includes the value , which does not satisfy the original condition.

  • If conditions that cover sets of records contained within intervals are combined with , they form a condition that covers a set of records contained within the union of their intervals. If the conditions are combined with , they form a condition that covers a set of records contained within the intersection of their intervals. For example, for this condition on a two-part index: 

    ( = 1 AND  < 2)
OR ( > 5)

    The intervals is: 

    (1, -inf) < (, ) < (1, 2)
(5, -inf) < (, )

    In this example, the interval on the first line uses one key part for the left bound and two key parts for the right bound. The interval on the second line uses only one key part. The column in the output indicates the maximum length of the key prefix used.

    In some cases, may indicate that a key part was used, but that might be not what you would expect. Suppose that and can be . Then the column displays two key part lengths for the following condition: 

     >= 1 AND  < 2

    But in fact, the condition is converted to this: 

     >= 1 AND  IS NOT NULL

Sección 7.2.5.1, “Método de acceso a rango para índices simples” describes how optimizations are performed to combine or eliminate intervals for range conditions on single-part index. Analogous steps are performed for range conditions on multiple-part keys.

7.2.6. Index Merge Optimization

The Index Merge () method is used to retrieve rows with several , , or scans and merge the results into one. This method is employed when the table condition is a disjunction of conditions for which , , or 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 output, this method appears as in the column. In this case, the column contains a list of indexes used, and contains a list of the longest key parts for those indexes.

Examples: 

SELECT * FROM  WHERE  = 10 OR  = 20;

SELECT * FROM 
    WHERE ( = 10 OR  = 20) AND =30;

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

SELECT * FROM t1, t2
    WHERE t1.=1
    AND (t2.=t1. OR t2.=t1.);

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

  • intersection

  • union

  • sort-union

The following sections describe these methods in greater detail.

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

  • If a range scan is possible on some key, an Index Merge is not considered. For example, consider this query: 

    SELECT * FROM t1 WHERE (goodkey1 < 10 OR goodkey2 < 20) AND badkey < 30;

    For this query, two plans are possible:

    1. An Index Merge scan using the condition.

    2. A range scan using the condition.

    However, the optimizer only considers the second plan. If that is not what you want, you can make the optimizer consider by using or . The following queries are executed using Index Merge: 

    SELECT * FROM t1 FORCE INDEX(goodkey1,goodkey2)
WHERE (goodkey1 < 10 OR goodkey2 < 20) AND badkey < 30;

SELECT * FROM t1 IGNORE INDEX(badkey)
WHERE (goodkey1 < 10 OR goodkey2 < 20) AND badkey < 30;

  • If your query has a complex clause with deep / nesting and MySQL doesn't choose the optimal plan, try distributing terms using the following identity laws: 

    ( AND ) OR  = ( OR ) AND ( OR )
( OR ) AND  = ( AND ) OR ( AND )

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

7.2.6.1. Index Merge Intersection Access Algorithm

This access algorithm can be employed when a clause was converted to several range conditions on different keys combined with , and each condition is one of the following:

  • In this form, where the index has exactly parts (that is, all index parts are covered): 

    = AND = ... AND =

  • Any range condition over a primary key of an or table.

Here are some examples: 

SELECT * FROM  WHERE  < 10 AND =20;

SELECT * FROM 
WHERE (=1 AND =2) AND =2;

The Index Merge intersection algorithm performs simultaneous scans on all used indexes and produces the intersection of row sequences that it receives from the merged index scans.

If all columns used in the query are covered by the used indexes, full table records are not retrieved and ( output contains in field in this case). Here is an example of such query: 

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

If the used indexes don't cover all columns used in the query, full records are retrieved only when the range conditions for all used keys are satisfied.

If one of the merged conditions is a condition over a primary key of an or table, it is not used for record retrieval, but is used to filter out records retrieved using other conditions.

7.2.6.2. Algoritmo de acceso a Index Merge Union Access Algorithm

The applicability criteria for this algorithm are similar to those for the Index Merge method intersection algorithm. The algorithm can be employed when the table's clause was converted to several range conditions on different keys combined with , and each condition is one of the following:

  • In this form, where the index has exactly parts (that is, all index parts are covered): 

    = AND = ... AND =

  • Any range condition over a primary key of an or table.

  • A condition for which the Index Merge method intersection algorithm is applicable.

Here are some examples: 

SELECT * FROM t1 WHERE =1 OR =2 OR =3;

SELECT * FROM  WHERE (=1 AND =2) OR
  (='foo' AND ='bar') AND =5;

7.2.6.3. Index Merge Sort-Union Access Algorithm

This access algorithm is employed when the clause was converted to several range conditions combined by , but for which the Index Merge method union algorithm is not applicable.

Here are some examples: 

SELECT * FROM  WHERE  < 10 OR  < 20;

SELECT * FROM 
     WHERE ( > 10 OR  = 20) AND =30;

The difference between the sort-union algorithm and the union algorithm is that the sort-union algorithm must first fetch row IDs for all records and sort them before returning any records.

7.2.7. Cómo optimiza MySQL IS NULL

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

SELECT * FROM  WHERE  IS NULL;

SELECT * FROM  WHERE  <=> NULL;

SELECT * FROM 
    WHERE = OR = OR  IS NULL;

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

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

This optimization can handle one for any key part.

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

SELECT * FROM t1 WHERE t1.a= 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 ...);

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

Note that the optimization can handle only one level. In the following query, MySQL uses key lookups only on the expression and is not able to use the key part on : 

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

7.2.8. Cómo MySQL optimiza DISTINCT

combined with needs a temporary table in many cases.

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

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

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

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

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

When combining with , MySQL stops as soon as it finds 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 is used before (which you can check with ), MySQL stops reading from (for any particular row in ) when the first row in is found: 

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

7.2.9. Cómo optimiza MySQL los LEFT JOIN y RIGHT JOIN

An LEFT JOIN join_condition is implemented in MySQL as follows:

  • Table is set to depend on table and all tables on which depends.

  • Table is set to depend on all tables (except ) that are used in the condition.

  • The condition is used to decide how to retrieve rows from table . (In other words, any condition in the clause is not used.)

  • All standard join optimizations are done, with the exception that a table is always read after all tables on which it depends. If there is a circular dependence, MySQL issues an error.

  • All standard optimizations are done.

  • If there is a row in that matches the clause, but there is no row in that matches the condition, an extra row is generated with all columns set to .

  • If you use to find rows that don't exist in some table and you have the following test: IS NULL in the part, where is a column that is declared as , MySQL stops searching for more rows (for a particular key combination) after it has found one row that matches the condition.

is implemented analogously to , with the roles of the tables reversed.

The join optimizer calculates the order in which tables should be joined. The table read order forced by and helps the join optimizer do its work much more quickly, because there are fewer table permutations to check. Note that this means that if you do a query of the following type, MySQL does a full scan on because the forces it to be read before : 

SELECT *
    FROM a,b LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key)
    WHERE b.key=d.key;

The fix in this case is reverse the order in and are listed in the clause: 

SELECT *
    FROM b,a LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key)
    WHERE b.key=d.key;

MySQL 5.0 performs the following optimization: If the condition is always false for the generated row, the is changed to a normal join.

For example, the clause would be false in the following query if were : 

SELECT * FROM t1 LEFT JOIN t2 ON (column1) WHERE t2.column2=5;

Therefore, it is safe to convert the query to a normal join: 

SELECT * FROM t1, t2 WHERE t2.column2=5 AND t1.column1=t2.column1;

This can be made faster because MySQL can use table before table if this would result in a better query plan. To force a specific table order, use .

7.2.10. Cómo optimiza MySQL ORDER BY

In some cases, MySQL can use an index to satisfy an clause without doing any extra sorting.

The index can also be used even if the does not match the index exactly, as long as all of the unused portions of the index and all the extra columns are constants in the clause. The following queries use the index to resolve the part: 

SELECT * FROM t1 ORDER BY ,,... ;
SELECT * FROM t1 WHERE = ORDER BY ;
SELECT * FROM t1 ORDER BY  DESC,  DESC;
SELECT * FROM t1
    WHERE =1 ORDER BY  DESC,  DESC;

In some cases, MySQL cannot use indexes to resolve the , although it still uses indexes to find the rows that match the clause. These cases include the following:

  • You use on different keys: 

    SELECT * FROM t1 ORDER BY , ;

  • You use on non-consecutive parts of a key: 

    SELECT * FROM t1 WHERE = ORDER BY ;

  • You mix and : 

    SELECT * FROM t1 ORDER BY  DESC,  ASC;

  • The key used to fetch the rows is not the same as the one used in the : 

    SELECT * FROM t1 WHERE = ORDER BY ;

  • You are joining many tables, and the columns in the are not all from the first non-constant table that is used to retrieve rows. (This is the first table in the output that doesn't have a join type.)

  • You have different and expressions.

  • The type of table index used doesn't store rows in order. For example, this is true for a index in a table.

With , you can check whether MySQL can use indexes to resolve the query. It cannot if you see in the column. See Sección 7.2.1, “Sintaxis de (Obtener información acerca de un )”.

In MySQL 5.0, a optimization is used that records not only the sort key value and row position, but the columns required for the query as well. This avoids reading the rows twice. The algorithm works like this:

  1. Read the rows that match the clause, as before.

  2. For each row, record a tuple of values consisting of the sort key value and row position, and also the columns required for the query.

  3. Sort the tuples by sort key value

  4. Retrieve the rows in sorted order, but read the required columns directly from the sorted tuples rather than by accessing the table a second time.

This algorithm represents an improvement over that used in some older versions of MySQL.

To avoid a slowdown, this optimization is used only if the total size of the extra columns in the sort tuple does not exceed the value of the system variable. (A symptom of setting the value of this variable too high is that you see high disk activity and low CPU activity.)

If you want to increase speed, first see whether you can get MySQL to use indexes rather than an extra sorting phase. If this is not possible, you can try the following strategies:

  • Increase the size of the variable.

  • Increase the size of the variable.

  • Change to point to a dedicated filesystem with large amounts of empty space. In MySQL 5.0, this option accepts several paths that are used in round-robin fashion. Paths should be separated by colon characters ('') on Unix and semicolon characters ('') on Windows, NetWare, and OS/2. You can use this feature to spread the load across several directories. Note: The paths should be for directories in filesystems that are located on different physical disks, not different partitions on the same disk.

By default, MySQL sorts all , , ... queries as if you specified , , ... in the query as well. If you include an clause explicitly that contains the same column list, MySQL optimizes it away without any speed penalty, although the sorting still occurs. If a query includes but you want to avoid the overhead of sorting the result, you can suppress sorting by specifying . For example: 

INSERT INTO foo
SELECT a, COUNT(*) FROM bar GROUP BY a ORDER BY NULL;

7.2.11. Cómo optimiza MySQL los GROUP BY

The most general way to satisfy a clause is to scan the whole table and create a new temporary table where all rows from each group are consecutive, and then use this temporary table to discover groups and apply aggregate functions (if any). In some cases, MySQL is able to do much better than that and to avoid creation of temporary tables by using index access.

The most important preconditions for using indexes for are that all columns reference attributes from the same index, and the index stores its keys in order (for example, this is a B-Tree index, and not a HASH index). Whether usage of temporary tables can be replaced by index access also depends on which parts of an index are used in a query, the conditions specified for these parts, and the selected aggregate functions.

There are two ways to execute a query via index access, as detailed in the following sections. In the first method, the grouping operation is applied together with all range predicates (if any). The second method first performs a range scan, and then groups the resulting tuples.

7.2.11.1. Loose index scan

The most efficient way is when the index is used to directly retrieve the group fields. With this access method, MySQL uses the property of some index types (for example, B-Trees) that the keys are ordered. This property allows use of lookup groups in an index without having to consider all keys in the index that satisfy all conditions. Since this access method considers only a fraction of the keys in an index, it is called a loose index scan . When there is no clause, a loose index scan reads as many keys as the number of groups, which may be a much smaller number than that of all keys. If the clause contains range predicates (see the discussion in Sección 7.2.1, “Sintaxis de (Obtener información acerca de un )” of the join type), a loose index scan looks up the first key of each group that satisfies the range conditions, and again reads the least possible number of keys. This is possible under the following conditions:

  • The query is over a single table.

  • The includes the first consecutive parts of the index (if instead of , the query has a clause, then all distinct attributes refer to the beginning of the index).

  • The only aggregate functions used (if any) are and , and all of them refer to the same column.

  • Any other parts of the index than those from the referenced in the query must be constants (that is, they must be referenced in equalities with constants), except for the argument of or functions.

The output for such queries shows in the column.

The following queries provide several examples that fall into this category, assuming there is an index on table : 

SELECT c1, c2 FROM t1 GROUP BY c1, c2;
SELECT DISTINCT c1, c2 FROM t1;
SELECT c1, MIN(c2) FROM t1 GROUP BY c1;
SELECT c1, c2 FROM t1 WHERE c1 <  GROUP BY c1, c2;
SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 >  GROUP BY c1, c2;
SELECT c2 FROM t1 WHERE c1 <  GROUP BY c1, c2;
SELECT c1, c2 FROM t1 WHERE c3 =  GROUP BY c1, c2;

The following queries cannot be executed with this quick select method, for the reasons given:

  • There are other aggregate functions than or , for example: 

    SELECT c1, SUM(c2) FROM t1 GROUP BY c1;

  • The fields in the clause do not refer to the beginning of the index, as shown here: 

    SELECT c1,c2 FROM t1 GROUP BY c2, c3;

  • The query refers to a part of a key that comes after the part, and for which there is no equality with a constant, an example being: 

    SELECT c1,c3 FROM t1 GROUP BY c1, c2;

7.2.11.2. Tight index scan

A tight index scan may be either a full index scan or a range index scan, depending on the query conditions.

When the conditions for a loose index scan are not met, it is still possible to avoid creation of temporary tables for queries. If there are range conditions in the clause, this method reads only the keys that satisfy these conditions. Otherwise, it performs an index scan. Since this method reads all keys in each range defined by the clause, or scans the whole index if there are no range conditions, we term it a tight index scan . Notice that with a tight index scan, the grouping operation is performed only after all keys that satisfy the range conditions have been found.

For this method to work, it is sufficient that, for all columns in a query referring to parts of the key coming before or in between parts of the key, there is a constant equality condition. The constants from the equality conditions fill in any “gaps” in the search keys so that it is possible to form complete prefixes of the index. These index prefixes can be then used for index lookups. If we require sorting of the result, and it is possible to form search keys that are prefixes of the index, MySQL also avoids extra sorting operations because searching with prefixes in an ordered index already retrieves all the keys in order.

The following queries do not work with the first method above, but still work with the second index access method (assuming we have the aforementioned index on table ):

  • There is a gap in the , but it is covered by the condition . 

    SELECT c1, c2, c3 FROM t1 WHERE c2 = 'a' GROUP BY c1, c3;

  • The does not begin with the first part of the key, but there is a condition that provides a constant for that part: 

    SELECT c1, c2, c3 FROM t1 WHERE c1 = 'a' GROUP BY c2, c3;

7.2.12. Cómo optimiza MySQL las cláusulas LIMIT

In some cases, MySQL handles a query differently when you are using and not using :

  • If you are selecting only a few rows with , MySQL uses indexes in some cases when normally it would prefer to do a full table scan.

  • If you use with , MySQL ends the sorting as soon as it has found the first rows of the sorted result, rather than sorting the entire result. If ordering is done by using an index, this is very fast. If a filesort must be done, all rows that match the query without the clause must be selected, and most or all of them must be sorted, before it can be ascertained that the first rows have been found. In either case, once the rows have been found, there is no need to sort any remainder of the result set, and MySQL does not do so.

  • When combining with , MySQL stops as soon as it finds unique rows.

  • In some cases, a can be resolved by reading the key in order (or doing a sort on the key) and then calculating summaries until the key value changes. In this case, does not calculate any unnecessary values.

  • As soon as MySQL has sent the required number of rows to the client, it aborts the query unless you are using .

  • quickly returns an empty set. This can be useful for checking the validity of a query. When using one of the MySQL APIs, it can also be employed for obtaining the types of the result columns. (This trick does not work in the MySQL Monitor, which merely displays in such cases; you should instead use or for this purpose.)

  • When the server uses temporary tables to resolve the query, the clause is used to calculate how much space is required.

7.2.13. Cómo evitar lecturas completas de tablas

The output from shows in the column when MySQL uses a table scan to resolve a query. This usually happens under the following conditions:

  • The table is so small that it is faster to perform a table scan than a key lookup. This is common for tables with fewer than 10 rows and a short row length.

  • There are no usable restrictions in the or clause for indexed columns.

  • You are comparing indexed columns with constant values and MySQL has calculated (based on the index tree) that the constants cover too large a part of the table and that a table scan would be faster. See Sección 7.2.4, “Optimización de las cláusulas por parte de MySQL”.

  • You are using a key with low cardinality (many rows match the key value) through another column. In this case, MySQL assumes that by using the key it probably does a lot of key lookups and that a table scan would be faster.

For small tables, a table scan often is appropriate. For large tables, try the following techniques to avoid having the optimizer incorrectly choose a table scan:

7.2.14. Velocidad de la sentencia INSERT

The time required for inserting a record is determined by the following factors, where the numbers indicate approximate proportions:

  • Connecting: (3)

  • Sending query to server: (2)

  • Parsing query: (2)

  • Inserting record: (1 x size of record)

  • Inserting indexes: (1 x number of indexes)

  • Closing: (1)

This does not take into consideration the initial overhead to open tables, which is done once for each concurrently running query.

The size of the table slows down the insertion of indexes by log , assuming B-tree indexes.

You can use the following methods to speed up inserts:

  • If you are inserting many rows from the same client at the same time, use statements with multiple lists to insert several rows at a time. This is considerably faster (many times faster in some cases) than using separate single-row statements. If you are adding data to a non-empty table, you may tune the variable to make data insertion even faster. See Sección 5.3.3, “Variables de sistema del servidor”.

  • If you are inserting a lot of rows from different clients, you can get higher speed by using the statement. See Sección 13.2.4, “Sintaxis de ”.

  • With tables you can insert rows at the same time that statements are running if there are no deleted rows in the tables.

  • When loading a table from a text file, use . This is usually 20 times faster than using a lot of statements. See Sección 13.2.5, “Sintaxis de ”.

  • With some extra work, it is possible to make run even faster when the table has many indexes. Use the following procedure:

    1. Optionally create the table with .

    2. Execute a statement or a mysqladmin flush-tables command.

    3. Use myisamchk --keys-used=0 -rq . This removes all use of indexes for the table.

    4. Insert data into the table with . This does not update any indexes and therefore is very fast.

    5. If you intend only to read from the table in the future, use myisampack to compress it. See Sección 14.1.3.3, “Características de las tablas comprimidas”.

    6. Re-create the indexes with myisamchk -r -q . This creates the index tree in memory before writing it to disk, which is much faster because it avoids lots of disk seeks. The resulting index tree is also perfectly balanced.

    7. Execute a statement or a mysqladmin flush-tables command.

    Note that also performs the preceding optimization if you insert into an empty table; the main difference is that you can let myisamchk allocate much more temporary memory for the index creation than you might want the server to allocate for index re-creation when it executes the statement.

    In MySQL 5.0, you can also use DISABLE KEYS instead of myisamchk --keys-used=0 -rq and ENABLE KEYS instead of myisamchk -r -q . In this way, you can also skip the steps.

  • You can speed up operations that are done with multiple statements by locking your tables: 

    LOCK TABLES a WRITE;
INSERT INTO a VALUES (1,23),(2,34),(4,33);
INSERT INTO a VALUES (8,26),(6,29);
UNLOCK TABLES;

    This benefits performance because the index buffer is flushed to disk only once, after all statements have completed. Normally there would be as many index buffer flushes as there are statements. Explicit locking statements are not needed if you can insert all rows with a single statement.

    For transactional tables, you should use and instead of to obtain faster insertions.

    Locking also lowers the total time of multiple-connection tests, although the maximum wait time for individual connections might go up because they wait for locks. For example: 

    Connection 1 does 1000 inserts
Connections 2, 3, and 4 do 1 insert
Connection 5 does 1000 inserts

    If you don't use locking, connections 2, 3, and 4 finish before 1 and 5. If you use locking, connections 2, 3, and 4 probably do not finish before 1 or 5, but the total time should be about 40% faster.

    , , and operations are very fast in MySQL, but you can obtain better overall performance by adding locks around everything that does more than about five inserts or updates in a row. If you do very many inserts in a row, you could do a followed by an once in a while (about each 1,000 rows) to allow other threads access to the table. This would still result in a nice performance gain.

    is still much slower for loading data than , even when using the strategies just outlined.

  • To get some more speed for tables, for both and , enlarge the key cache by increasing the system variable. See Sección 7.5.2, “Afinar parámetros del servidor”.

7.2.15. Velocidad de las sentencias UPDATE

Update statements are optimized as a query with the additional overhead of a write. The speed of the write depends on the amount of data being updated and the number of indexes that are updated. Indexes that are not changed does not get updated.

Another way to get fast updates is to delay updates and then do many updates in a batch later. This is much quicker than doing one at a time if you lock the table.

Note that for a table that uses dynamic record format, updating a record to a longer total length may split the record. If you do this often, it is very important to use occasionally. See Sección 13.5.2.5, “Sintaxis de ”.

7.2.16. Velocidad de sentencias DELETE

The time to delete individual records is exactly proportional to the number of indexes. To delete records more quickly, you can increase the size of the key cache. See Sección 7.5.2, “Afinar parámetros del servidor”.

If you want to delete all rows from a table, use rather than . See Sección 13.2.9, “Sintaxis de ”.

7.2.17. Otros consejos sobre optimización

This section lists a number of miscellaneous tips for improving query processing speed:

  • Use persistent connections to the database to avoid connection overhead. If you can't use persistent connections and you are initiating many new connections to the database, you may want to change the value of the variable. See Sección 7.5.2, “Afinar parámetros del servidor”.

  • Always check whether all your queries really use the indexes you have created in the tables. In MySQL, you can do this with the statement. See Sección 7.2.1, “Sintaxis de (Obtener información acerca de un )”.

  • Try to avoid complex queries on tables that are updated frequently, to avoid problems with table locking that occur due to contention between readers and writers.

  • With tables that have no deleted rows, you can insert rows at the end at the same time that another query is reading from the table. If this is important for you, you should consider using the table in ways that avoid deleting rows. Another possibility is to run after you have deleted a lot of rows.

  • Use , , ... if you mostly retrieve rows in , , ... order. By using this option after extensive changes to the table, you may be able to get higher performance.

  • In some cases, it may make sense to introduce a column that is “hashed” based on information from other columns. If this column is short and reasonably unique, it may be much faster than a big index on many columns. In MySQL, it is very easy to use this extra column: 

    SELECT * FROM 
    WHERE =MD5(CONCAT(,))
    AND ='' AND ='';

  • For tables that change a lot, you should try to avoid all variable-length columns (, , and ). The table uses dynamic record format if it includes even a single variable-length column. See Capítulo 14, Motores de almacenamiento de MySQL y tipos de tablas.

  • It is normally not useful to split a table into different tables just because rows become large. To access a row, the biggest performance hit is the disk seek to find the first byte of the row. After finding the data, most modern disks can read the entire row quickly enough for most applications. The only case in which it is advantageous to split up a table is if the table is a table with dynamic record format (see above) that you can change to a fixed record size, or if you very often need to scan the table but do not need most of the columns. See Capítulo 14, Motores de almacenamiento de MySQL y tipos de tablas.

  • If you often need to calculate results such as counts based on information from a lot of rows, it may be preferable to introduce a new table and update the counter in real time. An update of the following form is very fast: 

    UPDATE  SET =+1 WHERE =;

    This is important when you use a MySQL storage engine such as that has only table-level locking (multiple readers / single writers). This gives better performance with most databases, because the row locking manager in this case has less to do.

  • If you need to collect statistics from large log tables, use summary tables instead of scanning the entire log table. Maintaining the summaries should be much faster than trying to calculate statistics “live”. Regenerating new summary tables from the logs when things change (depending on business decisions) is faster than changing the running application.

  • If possible, you should classify reports as “live” or as “statistical”, where data needed for statistical reports is created only from summary tables that are generated periodically from the live data.

  • When possible, take advantage columns' default values to insert values explicitly only when the value to be inserted differs from the default. This reduces the amount parsing required of MySQL and so improves the speed of insert operations.

  • In some cases, it is convenient to pack and store data into a column. In this case, you must provide code in your application to pack and unpack information, but this may save a lot of accesses at some stage. This is practical when you have data that does not conform well to a rows-and-columns table structure.

  • Normally, you should try to keep all data non-redundant (observing what is referred to in database theory as third normal form ). However, there may be situations in which it can be advantageous to duplicate information or create summary tables in order to gain more speed.

  • Stored procedures or UDFs (user-defined functions) may be a good way to gain performance for some tasks. See Capítulo 19, Procedimientos almacenados y funciones and Sección 27.2, “Añadir nuevas funciones a MySQL” for more information about these.

  • You can always gain something by caching queries or answers in your application and then performing many inserts or updates together. If your database supports table locks (like MySQL and Oracle), this should help to ensure that the index cache is only flushed once after all updates. You can also take advantage of MySQL's query cache to achieve similar results; see Sección 5.12, “La caché de consultas de MySQL”.

  • Use when you do not need to know when your data is written. This speeds things up because many records can be written with a single disk write.

  • Use when you want to give statements higher priority than your inserts.

  • Use to get retrievals that jump the queue. That is, the is executed even if there is another client waiting to do a write.

  • Use multiple-row statements to store many rows with one SQL statement (many SQL servers support this, including MySQL).

  • Use to load large amounts of data. This is faster than using statements.

  • Use columns to generate unique values.

  • Use once in a while to avoid fragmentation with tables when using a dynamic table format. See Sección 14.1.3, “Formatos de almacenamiento de tablas ”.

  • Use tables when possible to get more speed. See Sección 14.3, “El motor de almacenamiento ()”.

  • With Web servers, images and other binary assets should normally be stored as files. That is, store only a reference to the file rather than the file itself in the database. Most Web servers are better at caching files than database contents, so using files is generally faster.

  • Use in-memory tables for non-critical data that is accessed often, such as information about the last displayed banner for users who don't have cookies enabled in their Web browser. User sessions are another alternative available in many Web application environments for handling volatile state data.

  • Columns with identical information in different tables should be declared to have identical data types.

    Try to keep column names simple. For example, in a table named , use a column name of instead of . To make your names portable to other SQL servers, you should keep them shorter than 18 characters.

  • If you need really high speed, you should take a look at the low-level interfaces for data storage that the different SQL servers support. For example, by accessing the MySQL storage engine directly, you could get a speed increase of two to five times compared to using the SQL interface. To be able to do this, the data must be on the same server as the application, and usually it should only be accessed by one process (because external file locking is really slow). One could eliminate these problems by introducing low-level commands in the MySQL server (this could be one easy way to get more performance if needed). By carefully designing the database interface, it should be quite easy to support this types of optimization.

  • If you are using numerical data, it is faster in many cases to access information from a database (using a live connection) than to access a text file. Information in the database is likely to be stored in a more compact format than in the text file, so accessing it involves fewer disk accesses. You also save code in your application because you don't have to parse your text files to find line and column boundaries.

  • Replication can provide performance benefits for some operations. You can distribute client retrievals among replication servers to split up the load. To avoid slowing down the master while making backups, you can make backups using a slave server. See Capítulo 6, Replicación en MySQL.

  • Declaring a table with the table option makes index updates faster because they are not flushed to disk until the table is closed. The downside is that if something kills the server while such a table is open, you should ensure that they are okay by running the server with the option, or by running myisamchk before restarting the server. (However, even in this case, you should not lose anything by using , because the key information can always be generated from the data rows.)

previous page start next page