MySQL multi-column index optimization

All MySQL columns can use the index. Using indexes on related columns is the best way to improve the performance of select operations. Defines the maximum number of indexes and the maximum index length per table based on the storage engine. All storage engines support at least 16 indexes per table, with a total index length of at least 256 bytes. Using the Col_name (length) syntax in the index, you can create an index that uses only the first length characters of the char and Archar columns, and in this way only the prefix of the indexed column can index the file as much as a small one. The MyISAM and InnoDB storage engines also support indexing of blob and text columns, but the index length must be specified. Fulltext indexes for full-text search do not support local indexes.

Multi-column index:

MySQL can create multi-column indexes, one index supports 15 columns, and multi-column indexes are useful for multi-criteria queries. A multicolumn index can be seen as an array of sorted values that are created by connecting the values of indexed columns.

MySQL uses multi-column indexes in this way: queries are fast when the WHERE clause specifies a known number for the first column of the index, even if no other column value is specified.

CREATE INDEX Suoyin on Table (col1,col2)

Suoyin indexes are indexes for col1 and col2, which can be used for col1, or for col1,col2 queries that specify values in a known range.

SELECT * FROM test where col1= ' where ';

SELECT * FROM test where col1= ' Where1 ' and col2= "Where2";

SELECT * FROM test where col1= ' Where1 ' and (col2= ' where1 ' OR col2= ' where3 ');

SELECT * FROM test where col1= ' Where1 ' and col2 >= ' Where1 ' and col2 < ' where3 ';

But the multicolumn index is not used with the following query:

SELECT * FROM Test WHERE col2 = ' where ';

SELECT * FROM test where col1= ' where1 ' OR col2 = ' where3 ';

How MySQL uses the index:

Indexes are used to quickly identify rows that have a specific value in a column. Without an index, MySQL must start with the first record and then read the entire table until the related rows are found, and the larger the table, the more time it takes. If the columns queried in the table are indexed, MySQL does not have to look at all the data and quickly reach a location to search for the data. Most MySQL indexes are stored in the B-tree.

The index is used for the following operations

Quickly find the row that matches a WHERE clause.

When deleted.

When performing a join query when retrieving from another table

Find the max,min for the specific indexed column.

Sorts a group of keywords that are available.

When using

Tbl_name WHERE Col1=val1 and Col2=val2;

If the table has a multicolumn index, the optimizer can use the leftmost index prefix to find travel. For example, if you have a 3-column index (COL1,COL2,COL3), the search on (col1), (Col1,col2), and (COL1,COL2,COL3) has been indexed
If the column does not constitute the leftmost prefix of the index, MySQL cannot use the local index. Index (COL1,COL2,COL3)

SELECT * from table where col1=val1;

SELECT * FROM table where col1=val1 and col2=val2;

SELECT * Froom table where col2=val2; index not used

SELECT * FROM table where col2=val2 and col3=val3; Non-functional Index

SELECT * from Table WHERE col1=val1 OR col2=val2; Non-functional index.

You can also use the B-tree index for column comparisons in expressions through the =, >, >=, <, <=, or between operators. If the like parameter is a constant string that does not begin with a wildcard character, the index can also be used for like comparisons.

Like ' partten% '; using an index

Like ' parrtn%sf% '; Working with Indexes

Like '%parrtn '; The index is not useful because the prefix of the index value is not used with a% start.

Like Other_col does not use an index because it is not a constant value.

If the column is indexed, the search for Col_name is NULL also uses the index.

Any index that does not span all of the and levels in the WHERE clause is not used to refine the query, that is, to be able to use the index, all index prefixes must be used in each and group.

Use index below:

where Index_part1 = Val1 and Index_part2=val2;

where Index1=val1 or Index2=val2 and index3=val3; the columns here are index prefixes or single-column indexes.

Non-functional Index below:

where Index_part2 = Val2 and|or index_part3=val3; Index prefixes are not used.

where Index-part1 =val1 or index_part2=val2; No index spans all rows

two parts of the WHERE clause are not used in the wher index_part1 and a= ' a ' index

The hash index also has some other characteristics:

They are used only for equality comparisons (but soon ) using the = or <=> operator. They are used to compare operators, such as < of discovery range values.

The optimizer cannot use a hash index to speed up the order by operation

MySQL cannot determine approximately how many rows between two values (this is used by the range optimizer to determine which index to use). If you change a MyISAM table to a memory table of hash-index, some queries will be affected

You can only use the entire keyword to search for a row. (with a B-tree index, the leftmost prefix of any keyword can be used to locate rows

MySQL multi-column index optimization