What are the MySQL database index types?

Index type: B-tree index, hash index
The B-tree index accelerates data access because the storage engine does not scan the entire table to get the data that is needed. Instead, it starts at the root node. The root node holds pointers to child nodes, and the storage engine looks for data based on the pointer. It finds the correct pointer by finding the value in the node page, the node page contains a pointer to the child node, and the storage engine looks for the data based on the pointer. It finds the correct pointer by locating the values in the node page, which contains the upper and lower bounds of the values in the child nodes. Finally, the storage engine may not be able to find the data it needs, or it may successfully find the leaf page that contains the data.
• Example: B-tree indexes are useful for the following types of queries. Match full name, match leftmost prefix, match column prefix, match range value, exact match part, and match another part of a range;
Limitations of the B-tree index: It is of little use if the lookup does not start at the far left of the indexed column. Columns in the index cannot be skipped, and the storage engine does not have priority access to any of the columns to the right of the first scope condition. Example: If the query is where last_name= ' Smith ' and first_name like ' j% ' and dob= ' 1976-12-23 '; Access can use only the first two columns of the index, as the as is the scope condition.
• The hash index is based on a hash table and is useful only for precise lookups that use each column in the index. For each row, the storage engine calculates the hash code for the indexed column, which is a smaller value and may be different from the hash code of the other rows. It saves the hash code in the index and holds a pointer to each row in the hash table.

• Because the index contains only the hash code and the row pointer, not the value itself, MySQL cannot use the values in the index to avoid reading the rows.
MySQL cannot be sorted by using a hash index because they do not save rows sequentially.
• Hash indexes do not support partial key matching because they are computed by the full value of the index. That is, if there is an index on both columns (A, b), and only A is used in the WHERE clause, the index will not work.
• The hash index only supports equality comparisons that use = in (). They cannot speed up the range query. For example where price > 100;
• Accessing data in a hash index is very fast, unless the collision rate is high. When a collision occurs, the storage engine must access each row pointer in the linked list and then row-by-line data comparison to determine the correct data. If there are many collisions, some index maintenance operations may become slower.

What are the

MySQL database index types?