How to use mysql index

If MYSQL uses an index (from mysql reference)

The index is used to quickly find rows with a specific value in a column. If no index is used, MySQL must start with 1st records and read the entire table until related rows are found. The larger the table, the more time it takes. If the column to be queried in the table has an index, MySQL can quickly find the data file in the middle of a location, and there is no need to read all the data. If a table has 1000 rows, this is at least 100 times faster than sequential reading. Note that if you want to access most rows, sequential reading is much faster, because disk search is avoided. Most MySQL indexes (primary key, UNIQUE, INDEX, and FULLTEXT) are stored in Tree B. Only the R-tree is used for spatial column indexes, and the MEMORY table also supports hash indexes. The string automatically compresses the prefix and trailing space. In general, use indexes as discussed later. This section describes the features of a hash index (for MEMORY tables. The index is used for the following operations :? Quickly finds the rows matching a WHERE clause .? Delete a row. If you can select multiple indexes, MySQL usually uses the index with the least rows .? When the join operation is performed, the row is retrieved from other tables .? Index-specific columns Key_colFind the MAX () or MIN () value. Optimized by the pre-processor to check whether the index is in the key _ ColPreviously, WHERE was used for all keyword elements. Key_part _#= Constant. In this case, MySQL performs a keyword search for each MIN () or MAX () expression and replaces it with a constant. If all expressions are replaced with constants, the query returns immediately. Example :? Select min ( Key_part2), MAX ( Key_part2) FROM Tbl_nameWHERE Key_part1= 10 ;? If you sort or group the prefixes on the leftmost sides of an available keyword (for example, order by key_part_1, key_part_2), sort or group a table. If DESC is followed by all keyword elements, the keywords are read in reverse order. See section 7.2.12, "How does MySQL optimize order ".? In some cases, you can optimize a query so that you can retrieve values without querying data rows. If the query only uses a numeric column from a table that forms the leftmost prefix of some keywords, you can retrieve the value from the index tree for faster speed .? SELECT Key_part3FROM Tbl_name? WHERE Key_part1= 1 assume that you execute the following SELECT statement:

mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

If a multi-column index exists on col1 and col2, you can retrieve the corresponding row directly. If a single column index exists on col1 and col2, the optimizer tries to determine which index will find fewer rows to find more restrictive indexes and use this index to retrieve rows. If a table has multiple column indexes, the optimizer can use the leftmost index prefix to search for travel. For example, if there is a three-column index (col1, col2, col3), the pair (col1), (col1, col2), and (col1, col2, col3) the search on is indexed.

If the column does not constitute the leftmost index prefix,MySQLYou cannot use a local index. Assume that the following SELECT statement is available:

SELECT * FROM tbl_name WHERE col1=val1;SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;SELECT * FROM tbl_name WHERE col2=val2;SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

If (col1, col2, col3) has an index, only the first two queries use the index. 3rd and 4th queries do include indexed columns, but (col2) and (col2, col3) are not the leftmost prefixes of (col1, col2, col3.

You can also use the B-tree index to compare columns in EXPRESSIONS THROUGH THE =,>,> =, <, <=, or BETWEEN operator. If the LIKE parameter is a constant string that does not start with a wildcard, the index can also be used for LIKE comparison. For example, the following SELECT statement uses an index:

SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%';SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%';

In the 1st statements, only 'Patrick '<= key _ is considered _ Col<'Patterl' line. In the 2nd statements, only 'pat '<= key _ is considered _ Col<'Pau' row. The following SELECT statement does not use indexes:

SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';SELECT * FROM tbl_name WHERE key_col LIKE other_col;

In the first statement, the LIKE value starts with a wildcard character. In the second statement, the LIKE value is not a constant. If you use... LIKE '% String% 'And StringMore than 3 characters, MySQL uses Turbo Boyer-Moore AlgorithmInitialize the string mode and use this mode for faster search. If Col_nameIndexed, use Col_nameIf the search is null, indexes are used. Any indexes that do not span all AND levels in the WHERE clause are not used to optimize queries. In other words, to be able to use indexes, you must use the index prefix in each AND group. The following WHERE clause uses indexes:

... WHERE index_part1=1 AND index_part2=2 AND other_column=3    /* index = 1 OR index = 2 */... WHERE index=1 OR A=10 AND index=2    /* optimized like "index_part1='hello'" */... WHERE index_part1='hello' AND index_part3=5    /* Can use index on index1 but not on index2 or index3 */... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;

The following WHERE clause does not use an index:

/* index_part1 is not used */... WHERE index_part2=1 AND index_part3=2     /*  Index is not used in both parts of the WHERE clause  */... WHERE index=1 OR A=10     /* No index spans all rows  */... WHERE index_part1=1 OR index_part2=10

Sometimes MySQL does not use indexes, even if there are available indexes. One scenario is when the optimizer estimates that using indexes will require MySQL to access most rows in the table. (In this case, table scanning may be faster because fewer searches are required ). However, if this type of query uses LIMIT to search for only some rows, MySQL uses an index because it can locate several rows faster and return results. The Hash index has some other features :? They are only used for Equality comparison using the = or <=> operator ( Soon). They are used for comparison operators, such as the <.? The optimizer cannot use hash indexes to accelerate the order by operation. (This type of index cannot be used to search for the next entry in order ).? MySQL cannot determine the number of 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 hash-index MEMORY table, some queries will be affected .? You can only use the entire keyword to search for a row. (Use B-tree indexes. You can use the leftmost prefix of any keyword to find the row ). DescriptionEXPLAIN Tbl_nameOr: EXPLAIN [EXTENDED] SELECT Select_optionsThe EXPLAIN statement can be used as a synonym for DESCRIBE, or obtain information about how MySQL executes the SELECT statement :? EXPLAIN Tbl_nameYesdescribe Tbl_nameOr SHOW COLUMNS FROM Tbl_nameIs a synonym .? If the keyword "EXPLAIN" is put before the SELECT statement, MySQLIt explains how it processes SELECT and provides the order of how tables are joined and joined. This section describes the 2nd usage of EXPLAIN. With the help of EXPLAIN, you can know when to add an index to the table to obtain a faster SELECT statement that uses indexes to search for records. If incorrect indexes are used, run analyze table to update the TABLE statistics (such as the keyword set trend), which will affect the optimizer's selection. You can also know whether the optimizer joins the table in an optimal order. To force the optimizer to set a SELECT statement to join in the table naming order, the statement should start with STRAIGHT_JOIN, not just SELECT. EXPLAIN returns a row of information for each table in the SELECT statement. Tables are listed in the order they are read by MySQL during query processing. MySQLScan multiple links once ( Single-sweep multi-join. This means MySQLRead a row from the first table, find a matching row in the second table, and then in the 3rd tables. After all the tables are processed, It outputs the selected columns and returns the table list until it finds a table with more matching rows. Read the next row from the table and continue processing the next table. When the EXTENDED keyword is used, EXPLAIN generates additional information, which can be viewed using show warnings. This information shows the optimizer limits the tables and column names in the SELECT statement, what the SELECT statement looks like after rewriting and executing the optimization rule, and may include other annotations of the optimization process. Each output row of EXPLAIN provides information about a table, and each row includes the following columns :? IdSELECT identifier. This is the serial number of the SELECT query .? Select_typeSELECT type, which can be any of the following: o simple select (do not use UNION or subquery) the second or subsequent SELECT statement in the SELECT statement o dependent unionunion in o PRIMARY's outermost SELECTo UNIONUNION depends on the query results of o union resultunion in the outer query. O In the SUBQUERY, the first SELECT in the first SELECTo dependent subquery depends on the SELECT (subquery of the from clause) of the Oracle DERIVED export table in the external query )? The table referenced by the row output by the table .? Type join type. The following lists various join types, sorted by the best type to the worst type: the o system table has only one row (= the system table ). This is a special case of the const join type. The o const table can have at most one matching row, which will be read at the beginning of the query. Because there is only one row, the column value in this row can be considered as a constant by the rest of the optimizer. Const tables are fast because they are read only once! Const is used to compare all the parts of a primary key or UNIQUE index with a constant value. In the following query, Tbl_nameIt can be used for const tables:

SELECT * from tbl_name WHERE primary_key=1； SELECT * from tbl_name WHERE primary_key_part1=1 and  primary_key_part2=2；

O eq_ref reads a row from the table for each row combination from the preceding table. This may be the best join type except the const type. It is used to join all parts of an index and the index is UNIQUE or primary key. Eq_ref can be used to compare indexed columns with the = Operator. The comparison value can be a constant or an expression that uses the column of the table read before the table. In the following example, MySQL can use the eq_ref join for processing. Ref_tables:

 

O ref for each row combination from the preceding table, all rows with matching index values will be read from this table. If the join only uses the leftmost prefix of the KEY, or if the KEY is not UNIQUE or primary key (in other words, if the join cannot select a single row based on the keyword), use ref. If the key used matches only a few rows, the join type is good. Ref can be used for indexed columns using the = or <=> operator. In the following example, MySQL can use the ref join for processing. Ref_tables: