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 iskey 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!
