Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Oracle Composite Indexes and MySQL Multiple-Column Indexes

Oracle Usage

An index created on multiple table columns is known as a multi-column, concatenated, or composite index. The main purpose of composite indexes is to improve the performance of data retrieval for SELECT statements when filtering on all, or some, of the composite index columns. When using composite indexes, it is beneficial to place the most restrictive columns at the first position of the index to improve query performance. Column placement order is crucial when using composite indexes because the most prevalent columns are accessed first.

Examples

Create a composite index on the HR.EMPLOYEES table.

CREATE INDEX IDX_EMP_COMPI ON
  EMPLOYEES (FIRST_NAME, EMAIL, PHONE_NUMBER);

Drop a composite index.

DROP INDEX IDX_EMP_COMPI;

For more information, see Composite Indexes in the Oracle documentation.

MySQL Usage

MySQL multiple-column indexes are similar to composite indexes in Oracle.

These indexes are beneficial when queries filter on all indexed columns, the first indexed column, the first two indexed columns, the first three indexed columns, and so on. When indexed columns are specified in the optimal order during index creation, a single multiple-column index can improve performance in scenarios where several queries access the same database table.

You can specify up to 16 columns when creating a multiple-column index.

Examples

Create a multiple-column index on the EMPLOYEES table.

CREATE INDEX IDX_EMP_COMPI
  ON EMPLOYEES (FIRST_NAME, EMAIL, PHONE_NUMBER);

Drop a multiple-column index.

DROP INDEX IDX_EMP_COMPI;

For more information, see Multiple-Column Indexes in the MySQL documentation.