Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 33.9Mb
PDF (A4) - 34.0Mb
PDF (RPM) - 33.2Mb
EPUB - 8.5Mb
HTML Download (TGZ) - 8.2Mb
HTML Download (Zip) - 8.2Mb
HTML Download (RPM) - 7.1Mb
Eclipse Doc Plugin (TGZ) - 9.0Mb
Eclipse Doc Plugin (Zip) - 11.1Mb
Man Pages (TGZ) - 219.4Kb
Man Pages (Zip) - 322.3Kb
Info (Gzip) - 3.2Mb
Info (Zip) - 3.2Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  Optimizing InnoDB Queries

8.5.6 Optimizing InnoDB Queries

To tune queries for InnoDB tables, create an appropriate set of indexes on each table. See Section 8.3.1, “How MySQL Uses Indexes” for details. Follow these guidelines for InnoDB indexes:

  • Because each InnoDB table has a primary key (whether you request one or not), specify a set of primary key columns for each table, columns that are used in the most important and time-critical queries.

  • Do not specify too many or too long columns in the primary key, because these column values are duplicated in each secondary index. When an index contains unnecessary data, the I/O to read this data and memory to cache it reduce the performance and scalability of the server.

  • Do not create a separate secondary index for each column, because each query can only make use of one index. Indexes on rarely tested columns or columns with only a few different values might not be helpful for any queries. If you have many queries for the same table, testing different combinations of columns, try to create a small number of concatenated indexes rather than a large number of single-column indexes. If an index contains all the columns needed for the result set (known as a covering index), the query might be able to avoid reading the table data at all.

  • If an indexed column cannot contain any NULL values, declare it as NOT NULL when you create the table. The optimizer can better determine which index is most effective to use for a query, when it knows whether each column contains NULL values.

  • You can optimize single-query transactions for InnoDB tables, using the technique in Section 8.5.3, “Optimizing InnoDB Read-Only Transactions”.

  • If you often have recurring queries for tables that are not updated frequently, enable the query cache:

    [mysqld]
    query_cache_type = 1
    query_cache_size = 10M
    

User Comments
Sign Up Login You must be logged in to post a comment.