Can mysql create a joint index?

mysql can create joint indexes. MySQL allows users to create a joint index containing up to 16 columns. There are two creation methods: 1. Create when creating a table, the syntax "CREATE TABLE table name (column name 1 type PRIMARY KEY, column name 2 type, column name 3 type, ...INDEX index name (column name 2, column name 3...));"; 2. Create when modifying the table, the syntax "CREATE INDEX index name ON table name (column name 2, column name 3, column name 4 );".

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

What is a joint index? The essence of a joint index: The leftmost matching

The index on two or more columns is called a joint index, and the joint index is also called a composite index. MySQL allows users to create a composite index containing up to 16 columns.

For composite indexes: Mysql uses the fields in the index from left to right. A query can only use part of the index, but only the leftmost part.

For example, the index is key index (a,b,c). It can support 3 combinations of a | a,b| a,b,c for search, but does not support b,c Perform a search. Indexes are efficient when the leftmost field is a constant reference.

The query optimizer uses composite indexes for queries that test all columns in the index, or queries that test the first column, the first two columns, etc.

A single composite index can speed up these types of queries on the same table if the columns are specified in the correct order in the index definition.

Create a joint index when creating a table

Syntax:

CREATE TABLE 表名 (
    c1 data_type PRIMARY KEY,
    c2 data_type,
    c3 data_type,
    c4 data_type,
    INDEX 索引名 (c2,c3,c4)
);

In this syntax, the joint index consists of three columns c2, c3 and c4.

Create a composite index when modifying the table

You can use the following CREATE INDEX statement to add a composite index to an existing table:

CREATE INDEX 索引名 
ON 表名(c2,c3,c4);

Please note that if You have a composite index on (c1, c2, c3), you would index the search function on one of the following combinations:

(c1)
(c1,c2)
(c1,c2,c3)

For example:

SELECT
    *
FROM
    table_name
WHERE
    c1 = v1;
 
 
SELECT
    *
FROM
    table_name
WHERE
    c1 = v1 AND 
    c2 = v2;
 
 
SELECT  
    *
FROM
    table_name
WHERE
    c1 = v1 AND 
    c2 = v2 AND 
    c3 = v3;

If the columns do not form an index 's leftmost prefix, the query optimizer cannot use the index to perform a lookup. For example, the following query cannot be found using composites:

SELECT
    *
FROM
    table_name
WHERE
    c1 = v1 AND 
    c3 = v3;

MySQL Union Index Example

We will demonstrate using the employees table from the sample database .

The following statement creates a joint index on the lastName and firstName columns:

CREATE INDEX name 
ON employees(lastName, firstName);

• First, the name index can be used to specify the lastName value. lookup in the query because the lastName column is the leftmost prefix of the index.

• Secondly, the name index can be used to query the value of a combination of lastName and firstName values.

The name index is used to find in the following queries:

1) Find the employee with the last name Patterson

SELECT 
    firstName, 
    lastName, 
    email
FROM
    employees
WHERE
    lastName = 'Patterson';

This query uses the name index because The leftmost prefix of the index (i.e. the lastName column) is used for lookups.

You can verify this by adding a clause to the query via EXPLAIN:

EXPLAIN SELECT 
    firstName, 
    lastName, 
    email
FROM
    employees
WHERE
    lastName = 'Patterson';

Here is the output:

2) Find Employees with last name Patterson and first name Steve:

SELECT 
    firstName, 
    lastName, 
    email
FROM
    employees
WHERE
    lastName = 'Patterson' AND
    firstName = 'Steve';

In this query, both lastName and firstName columns are used for the lookup, therefore, it uses the name index.

Let’s check it out:

EXPLAIN SELECT 
    firstName, 
    lastName, 
    email
FROM
    employees
WHERE
    lastName = 'Patterson' AND
    firstName = 'Steve';

The output is:

3) Find employees whose surname is Patterson and whose first name is Steve or Mary:

SELECT 
    firstName, 
    lastName, 
    email
FROM
    employees
WHERE
    lastName = 'Patterson' AND
    (firstName = 'Steve' OR 
    firstName = 'Mary');

This query is similar to the second query, where both lastName and firstName columns are used for the lookup.

The following statement verifies index usage:

EXPLAIN SELECT 
    firstName, 
    lastName, 
    email
FROM
    employees
WHERE
    lastName = 'Patterson' AND
    (firstName = 'Steve' OR 
    firstName = 'Mary');

The output is:

The query optimizer cannot use the name index for lookups in the following query , because only the column whose firstName is not the leftmost prefix of the index is used:

SELECT 
    firstName, 
    lastName, 
    email
FROM
    employees
WHERE
    firstName = 'Leslie';

Similarly, the query optimizer cannot use the name index for the lookup in the following query because the firstName or lastName column is used for the lookup.

SELECT 
    firstName, 
    lastName, 
    email
FROM
    employees
WHERE
    firstName = 'Anthony' OR
    lastName = 'Steve';

[Related recommendations: mysql video tutorial]

The above is the detailed content of Can mysql create a joint index?. For more information, please follow other related articles on the PHP Chinese website!