When does MySQL use indexes or do not use indexes?

When does MySQL use indexes or do not use indexes?

Index:

You can use indexes to quickly access specific information in database tables. An index is a structure that sorts the values of one or more columns in a database table, for example, the name column of the employee table. If you want to search for a specific employee by name, the index will help you get the information faster than all rows in the table that must be searched.

An index is a separate, physical database structure. It is a set of one or more column values in a table and a logical pointer list pointing to the data page that physically identifies these values in the table.

The Index provides pointers to the data values stored in the specified column of the table, and then sorts these pointers according to the sort order you specify. The database uses an index in a similar way as you use an index in a book: it searches for an index to find a specific value, and then returns the pointer to the row containing the value.

In the database graph, you can create, edit, or delete each index type on the index/Key Attribute page of the selected table. When you save the table to which the index is attached or the relational graph of the table is saved, the index is saved in the database.

Note:

Not all databases use indexes in the same way. As a general rule, an index must be created on a table only when data in the index column is frequently queried. Indexes occupy disk space and speed up adding, deleting, and updating rows. In most cases, the speed advantage of indexing for data retrieval greatly exceeds its shortcomings. However, if the application updates data frequently or the disk space is limited, you may need to limit the number of indexes.

You can create an index based on a single or multiple column in a database table. Multiple-column indexes enable you to differentiate rows with the same value in one of the columns.

If you often search for two or more columns at the same time or sort by two or more columns, the index is also helpful. For example, if you often set a criterion for the first and second columns in the same query, it makes sense to create multiple columns of indexes in these two columns.

Determine the validity of the index:

• Check the WHERE and JOIN clauses of the query. Each column in any clause is an object that can be selected by the index.
• Test the new index to check its impact on running query performance.
• Consider the number of indexes created on the table. It is best to avoid having many indexes on a single table.
• Check the definitions of indexes created on the table. It is best to avoid overlapping indexes that contain shared columns.
• Check the number of unique data values in a column and compare the quantity with the number of rows in the table. The comparison result is the selectivity of the column, which helps to determine whether the column is suitable for creating an index. If so, determine the index type.

When does MySQL use indexes?

Use >,=,=,<,<=, if null and BETWEEN for a key code

1. SELECT * FROM table_name WHERE key_part1 = 1 and key_part2> 5;
2. SELECT * FROM table_name WHERE key_part1 is null;

When you use LIKE, which does not start with a wildcard

1. SELECT * FROM table_name WHERE key_part1 LIKE 'jani %'

Extract rows from another table during join

1. SELECT * from t1, t2 where t1.col = t2.key _ part

Find the MAX () or MIN () value of the specified index

1. Select min (key_part2), MAX (key_part2) FROM table_name where key_part1 = 10

The prefix of a key code uses order by or GROUP

1. SELECT * FROM foo order by key_part1, key_part2, key_part3

The time when all columns used in the query are part of the key code

1. SELECT key_part3 FROM table_name WHERE key_part1 = 1

When MySQL does not use Indexes

If MySQL can predict that it will be faster than scanning the entire table, no index will be used. For example, if key_part1 is evenly distributed between 1 and 100, it is not good to use indexes in the following queries:

1. SELECT * FROM table_name where key_part1> 1 and key_part1 <90

If you use the HEAP table and do not use = to search for all key code parts.

Use order by on the HEAP table.

If you do not use the first part of the key code

1. SELECT * FROM table_name WHERE key_part2 = 1

If you use LIKE, which starts with a wildcard

1. SELECT * FROM table_name WHERE key_part1 LIKE '% jani %'

Search for one index and create order by on another index

1. SELECT * from table_name WHERE key_part1 = # order by key

 

Add Note: When the query condition fields are the same, you can use OR range to query the index. Otherwise, the index is invalid.

For example:

1. SELECT * from table_name WHERE name = "A" OR name = "B"; (effective)
   
2. SELECT * from table_name WHERE name = "A" OR sex = "male"; (invalid)

Misunderstanding

1. If you do not add an index to a column that is commonly used in the where condition, one statement only takes one index.

2. If you create an index for multiple columns, which of the following columns can be queried? Meet left prefix requirements

Combined index (A1, A2, A3)

Where A = 1 (effective)

Where A = 1 and where A2 = 2 (effective)

Where A = 1 and where A2 = 2 and where A3 = 3 (effective)

Where A = 2 where A3 = 3 (not effective)

Where A = 1 and where A2> 2 and where A3 = 3 (A1, A2 take effect, A3 does not take effect)

Where A = 1 and where A2 like 'Jay % 'and where A3 = 3 (A1, A2 take effect, A3 does not take effect)

SQL Optimization

1. limit paging Optimization

2. clustered index: primary key. If no primary key exists, the first unique index is located (the values of all rows are not empty) if none of the above two conditions is met, a 6-byte id clustered index is automatically generated.

3. Secondary index: bookmarks containing key values will store the bookmarks.

4. Change count (*) to secondary index.