Make slow queries faster using compound indexes in MySQL

Let us first look at what is a composite index -

• A composite index is an index that is used on multiple columns.

• Also known as multi-column index.

• MySQL allows users to create composite indexes that can contain up to 16 columns.

• The query optimizer uses a composite index for queries, which tests all columns in the index.

• It can also be used to test queries for the first column, first two columns, etc.

• You can use a single composite index to speed up certain types of queries on the same table if you specify the columns in the correct order in the index definition.

Let us see how to create a composite index table in the process of creating a composite index. This can be done using the following statement -

Query

CREATE TABLE table_name (
   c1 data_type PRIMARY KEY,
   c2 data_type,
   c3 data_type,
   c4 data_type,
   INDEX index_name (c2,c3,c4)
);

In the above statement, the composite index consists of three columns c2, c3 and c4.

You can also use the "CREATE INDEX" statement to add a composite index to an existing table. Let's see how to do this -

Query

CREATE INDEX index_name
ON table_name(c2,c3,c4);

Let's see how to do sloq query quickly using composite index -

• Query The speed of execution depends on its duration.

• Using index hints can improve query speed.

• The MySQL optimizer can be used to make good decisions when selecting indexes.

• But this can only be done on static queries.

• If you add a query where the "WHERE" clause changes, the performance of the query will deteriorate because it won't let the optimizer do its job.

• The "FORCE INDEX" statement works like "USE INDEX (index_list)", in addition, table scan is considered an expensive task.

• A table scan is only required when a named index cannot be used to find a row in the table.

The above is the detailed content of Make slow queries faster using compound indexes in MySQL. For more information, please follow other related articles on the PHP Chinese website!