Indexes are used to quickly find rows that have a specific value in a column. Without indexing, MySQL must start with the 1th record and read through the entire table until the relevant rows are found. The larger the table, the more time it takes. If the query column in the table has an index, MySQL can quickly reach a location to find the middle of the data file, there is no need to look at all the data. If a table has 1000 rows, this is at least 100 times times faster than sequential reads. Note that if you need to access most rows, sequential reads are much faster because we avoid disk searches at this time.
Most MySQL indexes (PRIMARY KEY, UNIQUE, index, and fulltext) are stored in the B-tree. Only the index of the spatial column type uses the R-tree, and the memory table also supports the hash index.
strings automatically compress prefix and trailing spaces.
In general, use the index as discussed later. This section finally describes the characteristics of a hash index (for memory tables).
Indexes are used for the following actions:
? Quickly find a row that matches a WHERE clause.
? Deletes a row. If you can choose from multiple indexes, MySQL usually uses the index that finds the least rows.
? Retrieves rows from other tables when a join is performed.
? Find the Max () or min () value for the specific indexed column
Key_col . Optimized by the preprocessor to check whether the where
key_part_# =
constantis used for all keyword elements that occurred before Key_
Col in the index. In this case, MySQL performs a keyword lookup for each min () or max () expression and replaces it with a constant. If all expressions are replaced with constants, the query returns immediately. For example:
? SELECT MIN (
key_part2), MAX (
key_part2) from
tbl_name WHERE
key_part1= 10;
? Sort or Group A table if the leftmost prefix of an available keyword is sorted or grouped (for example, order by key_part_1,key_part_2). If Desc is followed by all key elements, the keyword is read in reverse order. See 7.2.12 section,"How MySQL optimizes order by. "
? In some cases, you can optimize a query so that you can retrieve values without querying the rows of data. For faster, you can retrieve values from the index tree if the query uses only the numeric type from a table and makes up the leftmost column of some of the keywords.
? SELECT
key_part3 from
tbl_name? WHERE
key_part1=1
Suppose you execute the following SELECT statement:
Mysql> SELECT * from Tbl_name WHERE col1=val1 and Col2=val2;
If there is a multiple-column index on the col1 and col2, you can remove the row directly. If a single-column index exists on the col1 and col2, the optimizer attempts to find a more restrictive index and use the index to fetch rows by deciding which index will find fewer rows.
If a table has a multiple-column index, the optimizer can use the leftmost index prefix to find a trip. 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 form the leftmost prefix of the index, MySQL local indexes cannot be used. Suppose you have the SELECT statement shown below:
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 2 queries use the index. The 3rd and 4th queries do include the indexed columns, but (col2) and (COL2,COL3) are not the leftmost prefixes (col1,col2,col3).
You can also pass an expression in the = , > , >= , < , <= or BETWEEN operator uses B - The tree index makes column comparisons. If the like argument is a constant string that does not begin with a wildcard character, the index can also be used to compare with a . For example, the following SELECT statement uses the index:
SELECT * from Tbl_name WHERE key_col like ' patrick% '; SELECT * from Tbl_name WHERE key_col like ' pat%_ck% ';
In the 1th statement, only the rows with the ' Patrick ' <=key_
col < ' patricl ' are considered. In the 2nd statement, only the lines with the ' Pat ' <=key_
col < ' Pau ' are considered.
The following SELECT statement does not use an index:
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-wildcard. In the second statement, the like value is not a constant.
If you use ... Like '%
string% ' and
string more than 3 characters, MySQL uses the
Turbo boyer-moore
algorithm Initializes the pattern of the string and then uses the pattern to search faster.
If
col_name is indexed, searches using
col_name is null will use the index.
Any index that does not span all and levels in the WHERE clause is not used to refine the query. In other words, in order to be able to use an index, the index prefix must be used in each and group.
The following WHERE clause uses an index:
... 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 the 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 isn't 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 indexes available. One scenario is when the optimizer estimates that using the index will require MySQL to access most of the rows in the table. (In this case, the table scan may be faster because fewer searches are required). However, if such a query uses limit to search only some of the rows, MySQL uses the index because it can find several rows faster and return them in the results.
The hash index has some other features:
? They are only used to compare (but
soon ) the equation of the = or <=> operator. They are used to compare operators, such as < that discover range values.
? The optimizer cannot use a hash index to speed up the order by operation. (This class index cannot be used to search for the next entry sequentially).
? MySQL cannot determine how many rows are between two values (which is used by the scope optimizer to determine which index to use). If you change a MyISAM table to a hash-indexed memory table, some queries will be affected.
? Only one row can be searched using the entire keyword. (with a B-tree index, the leftmost prefix of any keyword can be used to find rows).
EXPLAIN [EXTENDED] SELECT
select_options
The explain statement can be used as a synonym for describe or to obtain information about how MySQL executes a SELECT statement:
? EXPLAIN
tbl_name is a synonym for describe
tbl_name or show COLUMNS from
tbl_name .
? If you put the keyword explain in front of the SELECT statement,
MySQL explains how it handles select, providing the order of how tables are joined and joined.
This section explains the 2nd usage of explain.
With explain, you know when you have to add an index to a table to get a faster select that uses an index to find records.
If you are having problems with an incorrect index, you should run the Analyze table to update the table's statistics (such as the cardinality of the keyword set), which can affect the selection of the optimizer.
You can also know whether the optimizer joins tables in a best order. In order to force the optimizer to have a SELECT statement in the join order of the table naming order, the statement should begin with a straight_join rather than a select.
Explain returns a row of information for each table used in the SELECT statement. Tables are listed in the order in which they will be read by MySQL in the process of processing the query.
MySQL solves all joins in a way that scans multiple joins (
single-sweep multi-join) over and over again. This means that
MySQL reads a row from the first table, finds a matching row in the second table, and so on in table 3rd. When all the tables have been processed, it prints out the selected columns and returns to the table list until a table with more matching rows is found. Read the next line from the table and continue with the next table.
When using the Extended keyword, explain generates additional information that can be browsed with show warnings. This information shows what the optimizer qualifies the table and column names in the SELECT statement, what the SELECT statement looks like after overriding and executing the tuning rules, and may also include other annotations of the optimization process.
Each output row of the explain provides information about a table, and each row includes the following columns:
Select identifier. This is the query sequence number for the select.
Select type, which can be any of the following:
Simple select (Do not use union or subqueries)
The second or subsequent SELECT statement in the Union
The second or subsequent SELECT statement in the Union, depending on the query outside
The first select in a subquery
The first select in a subquery, depending on the query outside
To export a select of a table (a subquery FROM clause)
The table referenced by the row being exported.
The join type. The types of joins are given below, sorted by best type to worst type:
Table has only one row (= system table). This is a special case of the const join type.
The table has at most one matching row, and it will be read at the start of the query. Because there is only one row, the column values in this row can be considered constants by the remainder of the optimizer. The const table is quick because they are only read once!
Const is used to compare all parts of a primary key or a unique index with a constant value. In the following query,
tbl_name can be used in the const table:
SELECT * from Tbl_name where primary_key=1,
select * from Tbl_name where primary_key_part1=1 and Primary_key_part 2=2;
Read a row from the table for each combination of rows from the previous table. This may be the best join type, except for the const type. It is used in all parts of an index to be joined and the index is unique or primary KEY.
Eq_ref can be used to compare indexed columns with the = operator. A comparison value can be a constant or an expression that uses a column of a table that is read before the table.
In the following example, MySQL can use the EQ_REF join to handle the
ref_tables:
For each combination of rows from the preceding table, all rows with matching index values are read from this table. Use ref if the join uses only the leftmost prefix of the key, or if the key is not a unique or primary key (in other words, if the join cannot select a single row based on the keyword). If you use a key that matches only a few rows, the join type is good.
Ref can be used to use an indexed column of the = or <=> operator.
In the example below, MySQL can use a ref join to handle
ref_tables:
